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

[mwrdm]

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:

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.

[ALLUREAN]

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/

[rajulshah · Accepted solution]

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.