running total but if next value does not exist take the previous value

[Anonymous]

Hello guys,

hope someone can help me figuring this out.

This is what problem looks like.

As you can see running Total does not calculate the value of the column "measure".

For column "measure" I am using this code: (thanks again @Anonymous )

This meausre allows me to fill my missing value with the previous one. (Septemeber value == august value). 

1. Create a date dimension table

Date = CALENDAR(date(2022,1,1),TODAY())

2. Create a measure as below

Measure = 
VAR _selmonth =
    SELECTEDVALUE ( 'Date'[Date].[MonthNo] )
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Data] ), ALLSELECTED ( 'Table' ) )
VAR _month =
    MONTH ( _maxdate )
VAR _lastvalue =
    CALCULATE (
        SUM ( 'Table'[value] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Data] ) = YEAR ( _maxdate )
                && MONTH ( 'Table'[Data] ) = _month
        )
    )
RETURN
    IF ( _selmonth <= _month, SUM ( 'Table'[value] ), _lastvalue )

This is what my Data looks like: 

As you can see I got no data for september and so one, so this measure helped me filling these months with the last value of the previous month: august. 

Question:

So now I am trying to create a measure for running total:

I want the running total to sum up my values but if I dont have data for the next month, it should sum up the value of the last previous month (august in this case).

[PhilipTreacy · Accepted solution]

Hi @Anonymous 

Try this

RT = TOTALYTD(SUM('DataTable'[Value]),'Dates'[Date])

Download my example PBIX file.

This has a Date Table with the month name which is displayed in the table visual.  This column is sorted by the Month column (so it's sorted correctly rather than alphabetically)

regards

Phil

[amitchandak]

@Anonymous , if you have date/calendar table marked as calendar table, make sure the month in visual is used from date table ( Join with date table should be single directional)

a simple measure like this should work

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Refer the blog for other reasons

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4