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  What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date - The given dataset does not have Manpower by Date anywhere in the data? Can you revise your data?

sohaibnomani's avatar
sohaibnomani
Helper II
4 years ago

The fact table contains start date and end date. All the dates in between would have the same team (MA, EA, EB) while manpower is mentioned in the dimension table.

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