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

## 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.
3 REPLIES 3
Highlighted
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.
Highlighted
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

## 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?

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.

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Experience what’s next for Power BI

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

#### Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

#### 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