Running total in time

[Marcin]

Hi,

I need to get chart with runnig total hours reported in project by weekt time , I am using this  DAX formula :

CumulativeHours =
CALCULATE (
SUM ( Events[HoursRound] ),
FILTER ( ALL ( 'Time'[Week] ), 'Time'[Week] <= MAX ( 'Time'[Week] ) )
)

But the problem is that in time table I have weeks where there are no hours reported yet for this reason I get chart like first one 

I would like to stop runnig total in courrent week. 

Thanks for help. 

[dkay84_PowerBI]

Maybe try wrapping your running total in an IF() statement that checks if the date value is the current week or earlier and if yes, returns your cumulative value, and if not, returns BLANK().

An easy way to do this is to add a calc column that checks if the date is => current week and return a Yes or No flag, then use this field in your measure for the filter argument.

Also, you could try setting a visual level filter on the chart you have to only show dates up to the current date/week.

[Anonymous · Accepted solution]

Here is my theory:

CumulativeHours =
VAR LastEvent = LASTNONBLANK ( Time[Week], CALCULATE ( SUM ( Events[HoursRound] ) ) )
RETURN
  CALCULATE (
    SUM ( Events[HoursRound] ),
    FILTER ( ALL ( 'Time'[Week] ), 'Time'[Week] <= MAX ( 'Time'[Week] ) && 'Time'[Week] <= LastEvent)
   )

[Marcin]

That works, thanks a lot for help !