Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
That's brilliant! Thank you so much for your swift and incredibly helpful reply!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |