Complicated DateDiff question

[lidderdj]

Hi, 

I've tried many different ways to solve this issue, but I just can't crack it and hoping someone here has the answer. 

I have two tables, a date table:

Date =
ADDCOLUMNS (
CALENDAR (DATE(2016,1,1), DATE(2050,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort", FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)

And a table containing the following fields:

ID (a unique ID for work phases)

Phase End (a date field for the anticipated completion of the work phase)

Date Added (the date the record was added to the table)

Every month, new records are appended to the bottom of the table, so you get, for example:

ID      Phase End     Date Added

01      01/03/2018    01/01/2018

02      01/04/2018    01/01/2018

03      01/05/2018    01/01/2018

01      02/03/2018    01/02/2018

02      03/04/2018    01/02/2018 

03      31/04/2018    01/02/2018

04      01/05/2018    01/02/2018

I need a measure in the date table that sums the Phase End shift for each ID for each date added. For example:

Date                  Phase Shift (Days)

01/01/2018                0

01/02/2018                2       (1 day for ID 01 + 2 days for ID 02 - 1 day for ID 03) 

The Date Added field cannot have an active relationship with the date field in the date table as there is already an existing active relationship to a different table. 

Is this possible? 

Thank you so much in advance for your efforts, everyone. 

[Phil_Seamark · Accepted solution]

Hi @lidderdj

Why not try adding a calculated column to your table to track the phase shift.  Something like this

Phase Shift = 
VAR CurrentPhaseEnd = 'Table1'[Phase End]
VAR CurrentID = 'Table1'[ID]
VAR LastDateAddedForID = 
    MAXX(
        FILTER(
            'Table1',
            'Table1'[ID] = CurrentID &&
            'Table1'[Date Added] < EARLIER('Table1'[Date Added])
            ),'Table1'[Date Added])
VAR LastPhaseEnd = 
    MAXX(
        FILTER(
            'Table1',            
            'Table1'[ID] = CurrentID &&
            'Table1'[Date Added] = LastDateAddedForID 
        ),'Table1'[Phase End])
            
RETURN IF(NOT ISBLANK(LastPhaseEnd) , INT(CurrentPhaseEnd-LastPhaseEnd))

This will give you this

You can then drag the [date added' field to a visual and simply SUM the new column.

[lidderdj]

That's brilliant! Thank you so much for your swift and incredibly helpful reply!