Forum Discussion

lidderdj's avatar
lidderdj
Frequent Visitor
8 years ago
Solved

Complicated DateDiff question

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 (D...
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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.

Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
8 years ago

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's avatar
lidderdj
Frequent Visitor
8 years ago

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