Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
mwrdm
Frequent Visitor

Help with cumulative summing - rows that don't have a value

Hi all

I'm relatively new to Power BI and am trying to get a running total that covers every day even if the day doesn't have a value to sum. The calculation is for number of hours.

Here is how it currently looks:

 

mwrdm_3-1654574883496.png

25/06/2022 doesn't have any hours, but instead of showing 0 in the Running Total column it should show 29,436.00 until 30/06/2022.

The date column is from my date/calendar table and is joined to a date column in the table that holds the hours.

Here is the dax (measure) for the Running Total column. Thanks in advance for any help.

Running Total =
CALCULATE(
SUM('Plan Export'[Baseline Duration (Hours)]),
FILTER(
ALL('Plan Export'),
('Plan Export'[Baseline Start (Rounded)]) <= MAX ('Plan Export'[Baseline Start (Rounded)])
)
)

I've also tried this (measure again)... my brain is a bit mushy from looking at it for too long so it quite likely doesn't make sense!

 

Running Total =
VAR BaseLineHours = SUM('Plan Export'[Baseline Duration (Hours)])
VAR RunningTotalCalc =
CALCULATE(
BaseLineHours,
FILTER(
ALL('Plan Export'),
('Plan Export'[Baseline Start (Rounded)]) <= MAX ('Plan Export'[Baseline Start (Rounded)])
)
)
Return RunningTotalCalc

 

Thanks in advance for any help.

1 ACCEPTED SOLUTION
rajulshah
Super User
Super User

Hello @mwrdm ,

 

You can try the following code:

Running Total =
CALCULATE (
    SUM ( 'Plan Export'[Baseline Duration (Hours)] ),
    FILTER (
        ALL ( 'Plan Export' ),
        ( 'Plan Export'[Date] ) <= MAX ( 'Plan Export'[Date] )
    )
)

 

However, it is advisable to create a date dimension table and use that. 
Please let me know if this doesn't help.

View solution in original post

2 REPLIES 2
ALLUREAN
Solution Sage
Solution Sage

Hi, @mwrdm 

In case you have a date table e.g. 'Date'[Date], this part of the measure should be changed to:

*****

ALL('Date'[Date]), 

'Date'[Date] <= MAX('Date'[Date] ) )
*****

Please refer to my article https://allure-analytics.com/index.php/2022/05/24/time-intelligence-in-power-bi/

 




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


https://allure-analytics.com/
https://www.youtube.com/channel/UCndD_QZVNB_JWYLEmP6KrpA
https://www.linkedin.com/company/77757292/

Proud to be a Super User!




rajulshah
Super User
Super User

Hello @mwrdm ,

 

You can try the following code:

Running Total =
CALCULATE (
    SUM ( 'Plan Export'[Baseline Duration (Hours)] ),
    FILTER (
        ALL ( 'Plan Export' ),
        ( 'Plan Export'[Date] ) <= MAX ( 'Plan Export'[Date] )
    )
)

 

However, it is advisable to create a date dimension table and use that. 
Please let me know if this doesn't help.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.