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 start date and Calender date columns. Now when i use a visual bar chart with "calender day no. " on x axis and "manpower" on Y axis.

 

 

In this case, the sum of manpower is displayed only on dates corresponding to start date only(as start date is linked with calendar date). 

What I want is that the sum of manpower is displayed for the entire duration. for example If Team "MA" is working for a duration of 15 days,( row 1), then sum of manpowr of "MA" should reflect on day 1 (11/3/22) to day 15 (25/3/22)

 

Fact table

SR#     Dep    Team     Start Date                      End Date

1MMDMA11/03/2022 7:0025/03/2022 8:00
2MMDMA25/03/2022 10:0030/03/2022 19:00
1EMDEA14/03/2022 13:0014/03/2022 15:00
2EMDEB14/03/2022 13:0014/03/2022 15:00

both are linked with "team" column.

Dimension table

TeamManpower TypeManpower
MAMilwright2
MAFitter4
MAHelper6
MAScaffolder4
MARigger1
MASBM3
MAFME1
MAFabricator0
MAInsulator0
MATechnician0
EAMilwright0
EAFitter0
EAHelper0
EAScaffolder0
EARigger0
EASBM0
EAFME0
EAFabricator0
EAInsulator0
EATechnician15
EBMilwright0
EBFitter0
EBHelper0
EBScaffolder0
EBRigger0
EBSBM0
EBFME0
EBFabricator0
EBInsulator0
EBTechnician10
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]
)

     

     

     

15 Replies

  • ValtteriN's avatar
    ValtteriN
    Community Champion
    4 years ago

    Hi,

    Here is one way to do this:

    1. Create a new column to your fact table which displays teams manpower:

    ManPowerA =
    var team = TeamsFact[Team] return
    calculate(SUM(TeamComposition[Manpower]),TeamComposition[Team]=team,ALL(TeamComposition))


    2. Create following measure:

    Manpower =
    var c_date = MAX('Calendar'[Date])
    return
    CALCULATE(SUM(TeamsFact[ManPowerA]),
    FILTER(TeamsFact,TeamsFact[SDate]<=c_date &&
    TeamsFact[Edate]>=c_date))
     
    3. End result (note that I don't have an active relationship from fact to calendar table):


    The results check out with the data.

    I hope this helps and if it does consider accepting this as a solution and giving the post a thumbs up!

    • sohaibnomani's avatar
      sohaibnomani
      Helper II
      4 years ago

      Following is the result, which is same as before

       

      Applied formula for column

      for measure

      I have linked date column from calendar table to startdateonly from the Fact table, but when select "startdateonly" in the above visual chart, no data is displayed while with "day" of Calendar table, it works.

       

      • ValtteriN's avatar
        ValtteriN
        Community Champion
        4 years ago

        Do you need the relationship between start date and calendar for some other reason? If not you could disable it. That is likely causing the issue here.

  • smpa01's avatar
    smpa01
    Community Champion
    4 years ago

    sohaibnomani  do you mean this?

     

    _totalManpower = 
VAR _team =
    MAX ( _fact[Team] )
VAR _start =
    MAX ( _fact[Start Date] )
VAR _end =
    MAX ( _fact[End Date] )
VAR _tbl =
    FILTER (
        CROSSJOIN ( 'calendar', _fact ),
        _start <= 'calendar'[Date]
            && _end >= 'calendar'[Date]
    )
RETURN
    SUMX (
        ADDCOLUMNS (
            _tbl,
            "_sum",
                CALCULATE (
                    SUM ( _dimension[Manpower] ),
                    TREATAS ( { _team }, _dimension[Team] )
                )
        ),
        [_sum]
    )
    • sohaibnomani's avatar
      sohaibnomani
      Helper II
      4 years ago

      Thanks for your solution. However slight changes would be required in the code. In ur file, i have ploted _totalmanpower MEASURE against Calendar Date. I m getting results below. What is required is that the chart on x axis plotes all dates and show the sum of manpower on each date .I would be using dates manpower basically for the plot in order to see total manpower bar graph. A team filter would be applied later

       

       

      • 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?