Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi All
Im hoping this is really simple and im just not seeing it. I currently have a column (calculated on the SQL load) that works out the days since the "Date Due" column and GETDATE(). This works perfectly.
I now have a requirement to be able to make it dynamic so they can go back in time to see overdue items.
I have put a slicer on my report that filters my date table. My calculated column then does a datediff between the due date and the max of the date table., but its returning the very last date disregarding the slicer.
So at the min my formula is:
VAR MaxDate = MAX('Due Dates'[Date]) RETURN DATEDIFF('Customer Ledger'[DueDateInternal], MaxDate, DAY)
And im getting this:
EntryNumber | DueDate | DaysOverdue
123456 2019-01-01 29585
123457 2019-01-02 29584
123458 2019-01-03 29583
Any help would be greatly appreciated 🙂
Solved! Go to Solution.
hi, @MCornish
First, you must know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, you could use a measure instead of it.
Just use this formula to add a measrue
VAR MaxDate = MAX('Due Dates'[Date]) RETURN DATEDIFF(MAX('Customer Ledger'[DueDateInternal]), MaxDate, DAY)
Then drag them into visual.
Best Regard,
Lin
hi, @MCornish
First, you must know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/
Second, you could use a measure instead of it.
Just use this formula to add a measrue
VAR MaxDate = MAX('Due Dates'[Date]) RETURN DATEDIFF(MAX('Customer Ledger'[DueDateInternal]), MaxDate, DAY)
Then drag them into visual.
Best Regard,
Lin
Hi Lin
Thanks for this. I thought this was going to be the case (Calculated Columns cannot be effected by slicers).
My reason behind not using the measure initially was that I dont want the calculation to aggregate. Each line would be placed into a "bucket" (1-30, 31-60 etc) and used as a matrix header.
hi, @MCornish
You are welcome, Could you please tell me if you have other problem? If not, could you please mark the helpful replies as Answered?
Best Regards,
Lin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |