0

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.

Chris Wildstone
  • 1
  • 1
  • "I need to know the last day of the reporting period (ie, the use filter date)." This sounds like you don't have a Calendar table. Because a reporting period sounds like something you'd have in your Date dimension/Calendar table. – Pieter Aug 31 '23 at 00:20
  • I don't have a Calendar table, just the data tables that Sage provides. I am hoping to allow the user to filter based on the Date field from the Audit_Split table, and to use the date they choose to calculate the Aged periods. If you can explain how a Calendar table would help achieve what I want, I'd be happy to add it by importing from a spreadsheet, if necessary. – Chris Wildstone Aug 31 '23 at 09:44
  • you can create your own calendar table using DAX or using PowerQuery. Since I'm terrible at PowerQuery, I tend to use DAX. Goes something like this: DimDate = CALENDAR(MINX('FactTable'[DateColumn]), MAXX('FactTable',[DateColumn]) ) you can use ADDCOLUMNS() with that to add whatever columns you like that you may want to slice by... year, month, reporting period... – Pieter Aug 31 '23 at 15:24

0 Answers0