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

[chlxco]

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

Table

Item	Time	Start Date	End Date	Time 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

[v-janeyg-msft · Accepted solution]

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

[amitchandak]

@chlxco , On a similar issue , check if the attached file or the blog can help

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[chlxco]

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 

[YukiK]

So you're looking to get cumulative sum, correct?

If so this answer may help: https://community.powerbi.com/t5/Desktop/Calculating-Cumulative-Monthly-Totals/m-p/100756

Cumulative =
CALCULATE (
SUM ( YourMeasure ),
filter( ALL ( 'Dates' ),
'Dates'[Date] <= MAX( 'Dates'[Date] ))
)

Please give it a thums up if this helps!

[chlxco]

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]

Is this what you're looking for?

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