Running Total Measure that counts up to a certain hour

[aaronzheng]

Hello, I have a running total measure that doesn't add up to a specified time when filtered. Instead it just shows the value at that date and hour rather than the sum up to that hour. Also, I have a current hour flag column that marks as 1 is the hour is the current hour.

The visualization shows 40 corresponding to 9/8/21 at 8:00 am but should show 463 which is the sum of all of the applications on 9/8 up to 8:00 am. 

The dax for my measure is:

RT2 = CALCULATE(

SUM('FLA'[ApplicationCount])

,FILTER(

ALLSELECTED(FLA),

FLA[Time] <= MAX(FLA[Time])

     && FLA[Date] = MAX(FLA[Date])

     )

)

Please let me know if you need any more information.

[aaronzheng]

When I change ALLSELECTED TO ALL: 

RT2 = CALCULATE(

SUM('FLA'[ApplicationCount])

,FILTER(

ALL(FLA),

FLA[Time] <= MAX(FLA[Time])

     && FLA[Date] = MAX(FLA[Date])

     )

)

I get the correct runningtotal and runningtotal by current hour. However, I am not able to filter down by STATE for instance. Therefore, when I use ALLSELECTED it works for the runningtotal but not for the runningtotal by hour.

[v-kelly-msft]

Hi  @aaronzheng ,

That's right,since you have only one value showing on the line chart,it can only show the unique value,why not create 2 measures,combine yours and mine,showing together on the line chart?

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[aaronzheng]

Here is what I am trying to replicate:

This is a runningtotal filtered on 1 state. 

Currently I have this visual broken into two visuals on PowerBI (mostly because I don't know if there is a way to add the black line graph to a visual), 

My measure is RT2 and your measure is RT3. Like I mentioned before since your dax used ALL rather than ALLSELECTED it doesn't filter down. 

The correct total for 9/11 at 9:00 am (runningtotal on 9/11 up to 9:00 am) should be 425 as shown. 

RT2 doesn't correctly add up the applications up to 9:00am, rather just plotting the number of applications during that hour. And your measure RT3 doesn't filter down so it shows the runningtotal up to that hour without filters (Correct value though).

Thanks, please let me know if you have any more questions.

[v-kelly-msft]

Hi  @aaronzheng ,

My sample is too simple to find the issue,could you pls share a sample .pbix file for test?

Remember to remove the confidential information.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[aaronzheng]

Hello Kelly,

I think the problem boils down to the current hour flag only selects the application count for that hour, rather than the runningtotal up to that hour. How can I get the runningtotal to appear for that given hour?

[aaronzheng]

https://drive.google.com/file/d/1b8KhZly4bsQpga7FxRy52jY4t8kZML00/view?usp=sharing

I couldn't upload the pbix, so you can find it in there. I am trying to get the visualization on the bottom to add up the applications to the current hour and other selected hours when filtered by state.

[parry2k]

@aaronzheng what you have on x-axis?

[aaronzheng]

I have ApplicationHourEST on the x-axis

[v-kelly-msft]

Hi  @aaronzheng,

Check below measure :

Measure =
SUMX (
    FILTER (
        ALL ( 'ApplicationHourEST' ),
        'ApplicationHourEST'[Date] = MAX ( 'ApplicationHourEST'[Date] )
            && 'ApplicationHourEST'[Time] <= MAX ( 'ApplicationHourEST'[Time] )
    ),
    'ApplicationHourEST'[Count]
)

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my raeply as a solution!

[aaronzheng]

Hey Kelly,

Thanks for this. It gives me the correct runningtotal but not when I filter down. I tried changing ALL to ALLSELECTED but then I get the same results as my previous measure's runningtotal.

[parry2k]

@aaronzheng make your FLA Date column just a date not date/time and that's why it not working.

✨ Follow us on LinkedIn

Check my latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[aaronzheng]

Hi @parry2k thanks for the reply. Sorry I didn't include this earlier. The date column is just a date.