Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
MCornish
Responsive Resident
Responsive Resident

DateDiff between column and Max of date slicer

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 🙂

 

1 ACCEPTED SOLUTION
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-lili6-msft
Community Support
Community Support

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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? Smiley Very Happy

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.