cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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

Accepted Solutions
Highlighted
Community Support
Community Support

Re: DateDiff between column and Max of date slicer

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
Highlighted
Community Support
Community Support

Re: DateDiff between column and Max of date slicer

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

Highlighted
Responsive Resident
Responsive Resident

Re: DateDiff between column and Max of date slicer

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.

 

 

Highlighted
Community Support
Community Support

Re: DateDiff between column and Max of date slicer

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors