Forum Discussion

sohaibnomani's avatar
sohaibnomani
Icon for Helper II rankHelper 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
Icon for Helper II rankHelper 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
Icon for Community Champion rankCommunity 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.