Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lidderdj
Frequent Visitor

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 (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. 

1 ACCEPTED SOLUTION
Phil_Seamark
Employee
Employee

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

 

 

phase shift.png

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Phil_Seamark
Employee
Employee

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

 

 

phase shift.png

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


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.