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

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

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

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

Helpful resources

Announcements
Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Microsoft Ignite 768x460.png

Find your focus

Explore the latest tools,training sessions,technical expertise, networking and more.

Power BI Womens Summit 2021 768 x460.jpg

Interviews, learning sessions, allies, and more!

#PowerBIWomenSummit

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!