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]
)

     

     

     

smpa01's avatar
smpa01
Community Champion
4 years ago

sohaibnomani  based on the dataset that you provided what was the output you desired?

sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago

this result. on 14th EA and EB teams are also working so total count is 46

 

 

DateManpower
11/03/202121
12/03/202121
13/03/202121
14/03/202146
15/03/202121
16/03/202121
17/03/202121
18/03/202121
19/03/202121
20/03/202121
21/03/202121
22/03/202121
23/03/202121
24/03/202121
25/03/202121

 

  • smpa01's avatar
    smpa01
    Community Champion
    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

        I am unable to select "startdate" column in expression secion of "filter" command. Please guide.