How can I use a User Filtered Date to drive Calculations in Power BI

Question

I am trying to create an Aged Creditors report in Power BI, using data from Sage 50. I have extracted Account_Ref, Date and Gross_Amount from the Audit_Split table, to which I can then apply a user filter for Date, which will give me the correct Balance totals for each account.

My difficulty arises when I try to place any outstanding balance in the periods; Current, 30 days, 60 days etc, because in order to calculate which period to place a transaction, I need to know the last day of the reporting period (ie, the use filter date).

I believe that a custom field won't recalculate based on a user filter (assuming you can make use of the user date), but that a measure might be my solution.

Can Dax reference a user filter date at all?

Please help.

Answer 1

Okay, sorry! If all you need to do is calculate the age of each transaction or invoice (for example), and you want to compare it to a selected date (say from a date slicer), then you need to do two things:

Create a measure to harvest the selected date value:

SelectedDate = SELECTEDVALUE(DimDate[Date])

Create a calculated column in your "Invoices" table:

Age = INT([SelectedDate] - [Due Date])

Create a calculated column in your Transactions table that calculates the age of an item:

Age Bin = SWITCH(TRUE(),
        [Age]<30,"Less than 30",
        [Age]<60,"Between 30 and 59",
        [Age]<90,"Between 60 and 89",
        "90+")

Now that there's an "Age Bin", we can create a table of Invoices that count the number of invoices or sum the outstanding amounts by "age bin".

There. I think that may answer your question. Apologies for not understanding the first time.