Forum Discussion

sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago
Solved

Creating a Measure Start date, End date, duration

I have the following two tables, along with a Calender Table consisting of 30 calender dates (starting from 11/3/22 to 4/4/22)) with day no. (1,2,3,4,5). I have already created relationship between s...
measure
  • smpa01's avatar
    smpa01
    4 years ago

    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's avatar
sohaibnomani
Helper II
4 years ago

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.

 

1EMDEA14/03/2022 09:0014/03/2022 13:00
2EMDEA14/03/2022 13:0014/03/2022 17:00
sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago

This is i am getting, I don't know what mistake i am doing. getting same results every time.

 

 

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    sohaibnomani  as you can see the data that you provided and the code that I wrote return exactly you had as the picture.