Running Total with Blank Month Value

[ElenaJ]

Hi,

I created a measure for a running total by month, the coding used is:

Cumulative = CALCULATE(

    SUM('Schedule'[Forecast]),

    USERELATIONSHIP(CalendarM[Date],'Schedule'[Forecasted Date]),

    FILTER(

        ALLSELECTED('CalendarM'[Date]),

        ISONORAFTER('CalendarM'[Date], MAX('Schedule'[Forecasted Date]), DESC)

    )

)

However, the visual I have (1)skips the months with no value and (2) has blank value for some months.

I want a  running total for every month, if there is no value for that month, just carry the previous month's value forward. Would appreciate if someone can advise how to revise the DAX?

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Cumulative =
CALCULATE (
    SUM ( 'Schedule'[Forecast] ),
    USERELATIONSHIP ( CalendarM[Date], 'Schedule'[Forecasted Date] ),
    FILTER (
        ALLSELECTED ( 'CalendarM' ),
        CalendarM[Date] <= MAX ( CalendarM[Date] )
            && CalendarM[Year] = MAX ( CalendarM[Year] )
    )
)

[ElenaJ]

For example, in the schedule table, 7if we don't have any June dates in the Forecasted Date, how can we calculate a culmulative value without skipping June?

Forecasted DateForecast

Sunday, 1 May 2022	5
Friday, 1 July 2022	3
Monday, 1 August 2022	4
Thursday, 1 September 2022	7
Saturday, 1 October 2022	6
Tuesday, 1 November 2022	9

[Jihwan_Kim · Accepted solution]

Hi,

Thank you for your feedback.

Please check the below if it suits your requirement.

-Jun value shows in the table visualization

-only upto Nov values are shown.

Cumulative = 
VAR _lastdateinscheduletable =
    CALCULATE ( MAX ( Schedule[Forecasted Date] ), REMOVEFILTERS () )
RETURN
    CALCULATE (
        SUM ( 'Schedule'[Forecast] ),
        USERELATIONSHIP ( CalendarM[Date], 'Schedule'[Forecasted Date] ),
        FILTER (
            ALLSELECTED ( 'CalendarM' ),
            CalendarM[Date] <= MAX ( CalendarM[Date] )
                && CalendarM[Year] = MAX ( CalendarM[Year] )
                && MIN ( CalendarM[Date] ) <= _lastdateinscheduletable
        )
    )

[haoqiong55]

This answer has helped resolve my question too. Much appreciated! Now I'm trying to get a new measure for calculating the running total of previous year in same way. Could you provide some guidance? Thank you

[ElenaJ]

It worked! Amazing!

Many thanks for your help!

[ElenaJ]

Thanks for the suggestion.

My problem is that I created a 10 year calendar that link to the dates in the schedule table. While the schedule table only runs to a certain month in 2023. I want the running total to add up to the last date on the schedule table instead of the calendar table.

[Anonymous]

Hi @ElenaJ ,

Could you share which table are you using to derived month column in your matrix?

BR

[ElenaJ]

Sure. I created a ten year calendar table called calendarM (2020-2030) and linked it to my fact table schedule. Calendar M has date and month/year columns.