Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |