DAX: Calculate sum of DateDiffs between last date of slicer and dates in date column

[MartijnBosma]

Hopefully someone can help me with the following problem:

I need to calculate the sum of the date differences between the dates in a table column and the latest date in my slicer.

Let's say I'll slice out all days in January 2017, then:

sliceLastDate = 31-01-2017

My table might for example contain the following dates in its date column:

Date
05-12-2016
07-01-2017
15-01-2017
03-02-2017

Note that the first and the last row are outside my sliced perdiod:
They are not in January 2017

A row that is later than my sliceLastDate should not be used:
So the last row with 03-02-2017 should be disregarded

However, a row that is before my slices start date (like row 05-12-2016) should be used.

Therefore, in this case DAX should do the following:

DateDiff(05-12-2016, 31-01-2017, day)
+
DateDiff(07-01-2017, 31-01-2017, day)
+
DateDiff(15-01-2017, 31-01-2017, day)

which should be:
= 57 + 24 + 16 = 97

I find it hard to figure out how to do this in DAX.
Should I use something like this?:

SUMX
(
     MyTable;
     DateDiff
     (
          MyTable[Date];
          MAX(Date[Date])
     )
)

"MyTable" is joined on a date dimension table called "Date" which contains a row for every day

Kind regards,
Martijn

[jaygill]

Hello everyone, 

I'm fairly new to this community and DAX. and i have run into a problem at work. A help would be much appreciated.

 i'm trying to calculate number of days for particular year based on calender table that i have created.

For Example: I have 3 columns. 

Event, number of days and Date when this event started

Injury     30        25/21/2016  

Days in 2016 - 6

Days in 2017 - 24

Now for above case there are only 6 days needs to be counted in 2016 and rest of the days should automatically be counted in 2017. But i can't figure out how to do it. 

Thanks in Advance.

[AlbertoFerrari · Accepted solution]

Hi Marijn,

Your code looks fine, the only thing you need to check is that you take into account dates which happen to be earlier than the last date, which you can achieve with a simple CALCULATE and a FILTER on ALL dates. Something like this should work:

VAR
    SlicerDate = MAX ( Date[Date] )
RETURN
CACLULATE (
    SUMX (
         MyTable,
         DATEDIFF ( MyTable[Date], SlicerDate )
    ),
    FILTER ( ALL ( Date ), Date[Date] <= SlicerDate )
)

You can find many more examples about time intelligence in the "time patterns" section of our dax patterns website. On a small dataset this code works fine, on a very large you might have some performance issue, but we are talking about hundreds of millions of rows before you hit any problem, hopefully it is not your case.

Alberto Ferrari
http://www.sqlbi.com

[lavpinto]

This solution is great.

Is there a way i can calculate sum of workings between last date of slicer and dates in date column