Forum Discussion
Creating a Measure Start date, End date, duration
sohaibnomani use the following measure
Measure = SUMX ( SUMMARIZE ( ADDCOLUMNS ( FILTER ( CROSSJOIN ( 'calendar', _fact ), _fact[Start Date] <= 'calendar'[Date] && _fact[End Date] >= 'calendar'[Date] ), "sum", CALCULATE ( SUM ( _dimension[Manpower] ), TREATAS ( { CALCULATE ( MAX ( _fact[Team] ) ) }, _dimension[Team] ) ) ), [Date], [Team], [sum] ), [sum] )
sohaibnomani use the following measure
Measure =
SUMX (
SUMMARIZE (
ADDCOLUMNS (
FILTER (
CROSSJOIN ( 'calendar', _fact ),
_fact[Start Date] <= 'calendar'[Date]
&& _fact[End Date] >= 'calendar'[Date]
),
"sum",
CALCULATE (
SUM ( _dimension[Manpower] ),
TREATAS ( { CALCULATE ( MAX ( _fact[Team] ) ) }, _dimension[Team] )
)
),
[Date],
[Team],
[sum]
),
[sum]
)
One thing that I missed to mension. If in the above fact table we have another entery of EA team doing two activities in a single day but different time, then the measure should count it once, rather than twice. Meanwhile i will try ur existing solution. THanks for your swift replies.
| 1 | EMD | EA | 14/03/2022 09:00 | 14/03/2022 13:00 |
| 2 | EMD | EA | 14/03/2022 13:00 | 14/03/2022 17:00 |
I am unable to select "startdate" column in expression secion of "filter" command. Please guide.
sohaibnomani why do you want to select Start Date? follow the attached pbix
This is i am getting, I don't know what mistake i am doing. getting same results every time.
sohaibnomani as you can see the data that you provided and the code that I wrote return exactly you had as the picture.