Forum Discussion

chlxco's avatar
chlxco
Frequent Visitor
4 years ago
Solved

Measure using time not totalizing - Complex one.. I guess.

good morning community. Im facing the following challenge. Consider this model

Table

ItemTimeStart DateEnd DateTime x month
prj A 30 jan-2021 mar-2021 10
prj B 120 feb-2021 jul-2021 20
prj C 100 jan-2021 feb-2021 50

 

Time is split across the months of the prj. I also count with another table which is basically an autogenerated date-table that I use to filter time ranges( a.k.a Dates). That said, I have created a measure that works well when I add a line chart over time(date in X axis), showing per month the sum of the Time. i.e. 

Total Hours  for Jan-21 = 10+50 prjA+prjB = 60

Total Hours for Feb-21 = 10 + 20 + 50 = prjA+ prjB+ prjC = 80 and so on...

This is the measure formula Ive used:

Total Hours= CALCULATE(SUM(Table[Time x Month]),
FILTER(VALUES(Table[Start Date]),Table[Start Date]<=MAX(Dates[Date])),
FILTER(VALUES(Table[End Date]),Table[End Date]>=MIN(Dates[Date]))
)

 

Here is the issue: When I try to sum a range of dates in a bar chart (i.e. JAN to FEB 2021), I expect to have the total amount of hours forboth months (for this example it would be 60+80=140) However, what I got if I use Total Hours variable in the chart is the last month, meaning a bar with a value of 80. How can I sum months based off this scenario? Do I need to aad something to Total hours or its a matter of chaning the formula completely? I use a relative date filter to select the date range...Thanks in adv

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, chlxco 

     

    According to your description, Is the 'total' displayed incorrectly?

    If yes, you can try:

    Total Hours =
SUMX (
    SUMMARIZE (
        Table,
        [Start Date],
        [End Date],
        [Time x Month],
        "a",
            CALCULATE (
                SUM ( Table[Time x Month] ),
                FILTER (
                    VALUES ( Table[Start Date] ),
                    Table[Start Date] <= MAX ( Dates[Date] )
                ),
                FILTER ( VALUES ( Table[End Date] ), Table[End Date] >= MIN ( Dates[Date] ) )
            )
    ),
    [a]
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

6 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, chlxco 

     

    According to your description, Is the 'total' displayed incorrectly?

    If yes, you can try:

    Total Hours =
SUMX (
    SUMMARIZE (
        Table,
        [Start Date],
        [End Date],
        [Time x Month],
        "a",
            CALCULATE (
                SUM ( Table[Time x Month] ),
                FILTER (
                    VALUES ( Table[Start Date] ),
                    Table[Start Date] <= MAX ( Dates[Date] )
                ),
                FILTER ( VALUES ( Table[End Date] ), Table[End Date] >= MIN ( Dates[Date] ) )
            )
    ),
    [a]
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

    • chlxco's avatar
      chlxco
      Frequent Visitor
      4 years ago

      Hi YukiK,

       

      I tried without success. When I translated into my model it takes me to the same place where I started here. It just gives me the last month time, not te cumulative amount across months (like the Total Hours does it). The other challenge I found is that SUM does not accept Calculated Measures (Total Hours). Bottom line, I need to sum Total Hours based off of the Date range I filter. Thx

      • YukiK's avatar
        YukiK
        Impactful Individual
        4 years ago

        Is this what you're looking for?

         

        If looking to do cumulative amount by month, then you'd just change Start Date with Month

    • chlxco's avatar
      chlxco
      Frequent Visitor
      4 years ago

      Hi Amit

       

      thanks for sharing this. I went through the file and blog but the issue is not related to the ability to split days/months or even sum hours across a timeframe. that purpuse is achieved by the calculated measure Total Hours. The issue Im facing is about summing the hours across a date range. If I select 1 single month, it does the job, but when I select +1 months, the calculation just not retain the total of months. Maybe related to what I asked to YukiK below, how can I use a SUM with the calculated field Total Hours, as the DAX only allows Columns as parameter? thx again