Running Total - Last Full 12 Months (Calendar Months)

[Hammerhead]

I have a data model in Power BI that includes a standard Date table (created using T-SQL at the data source) and a Sales fact table.

I need to create a line chart with Sales on the y-axis and Month on the x-axis, where the x-axis shows only the month name (not the year). The chart should display Sales for the last full 12 months and the last full 13-24 months simultaneously.

Additionally, I need to include the **running totals** for these same time periods on the chart. This is where I am encountering some challenges.

In my Date table, I’ve created fields [In Last Full 12 Months] and [In Last Full 13-24 Months], which return `TRUE` or `FALSE` based on whether a given date falls within those time periods. The measure for Sales (non-running total) works correctly for these periods.

Sales: Last Full 12 Months (€) =
CALCULATE (
    [Total Sales (€)],
    FILTER ( 'Date', 'Date'[In Last Full 12 Months] = TRUE() )
)

To clarify, the Last Full 12 Months refers to the 12-month period ending with the previous month, excluding the current month. For example, as today is 16.12.2024, the period would cover 01.12.2023 to 30.11.2024.

The following table illustrates the desired outcome:

I've tried the following measure, but this resets from January onwards:

Accumulated Sales: Last Full 12 Months (€) = 

CALCULATE(
    [Sales: Last Full 12 Months (€)],
    FILTER(
        ALL('Date'), -- Includes the entire 'Date' table for filtering
        'Date'[Date] <= MAX('Date'[Date])
    )
)

Any help would be greatly appreciated.

[danextian]

hI @Hammerhead 

In your last measure, you are trying to apply a filter context on a table which already has an existing  filter to so you're getting an unexpected result.

Try the following measures:

Sales = 
SUM ( Sales[Sales Amount] )

Sales L12M Excluding Current Month = 
CALCULATE (
    [Sales],
    DATESINPERIOD (
        'Date'[Date],
        --previous end of month date
        EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -1 ), 0 ),
        -12,
        MONTH
    ),
    REMOVEFILTERS ( 'Date' )
)


Sales L13-24M Excluding Current Month = 
CALCULATE (
    [Sales],
    DATESINPERIOD (
        'Date'[Date],
        --end of month 13 months ago
        EOMONTH ( EDATE ( MAX ( 'Date'[Date] ), -13 ), 0 ),
        -12,
        MONTH
    ),
    REMOVEFILTERS ( 'Date' )
)


Running Total = 
CALCULATE (
    [Sales],
    FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )
)

Details are in the attached pbix.

[Hammerhead]

Thank you for your help! However, the running total needs to reset to 0 at the start of each respective time period.

For example, the Last Full 12 Months Running Total should only accumulate within the context of the actual last full 12 months period. If it includes data outside of this range, it won't work correctly in the chart.

The line chart should eventually look like the following:

[Anonymous · Accepted solution]

Hi, @Hammerhead 

You can try the following methods.

Last Full 12 Months = 
VAR _curr_yearmonthno = CALCULATE(MAX('Date'[YearMonthNO]),'Date'[Date]=TODAY())
VAR _end_yearmonthno = _curr_yearmonthno - 1
VAR _start_yearmonthno = _curr_yearmonthno - 12
Var _runningsum=CALCULATE([Sales], FILTER(ALL('Date'),[YearMonthNO]>=_start_yearmonthno && [YearMonthNO]<=_end_yearmonthno&&[YearMonthNO]<=SELECTEDVALUE('Date'[YearMonthNO])))
RETURN
IF(SELECTEDVALUE('Date'[YearMonthNO])>=_start_yearmonthno&&SELECTEDVALUE('Date'[YearMonthNO])<=_end_yearmonthno,_runningsum,BLANK())

Last full 13-24 months = 
VAR _curr_yearmonthno = CALCULATE(MAX('Date'[YearMonthNO]),'Date'[Date]=TODAY())
VAR _end_yearmonthno = _curr_yearmonthno - 13
VAR _start_yearmonthno = _curr_yearmonthno - 24
Var _runningsum=CALCULATE([Sales], FILTER(ALL('Date'),[YearMonthNO]>=_start_yearmonthno && [YearMonthNO]<=_end_yearmonthno&&[YearMonthNO]<=SELECTEDVALUE('Date'[YearMonthNO])))
RETURN
IF(SELECTEDVALUE('Date'[YearMonthNO])>=_start_yearmonthno&&SELECTEDVALUE('Date'[YearMonthNO])<=_end_yearmonthno,_runningsum,BLANK())

Then a new month table needs to be created.

Table:

Month table = DISTINCT('Date'[Month])

Measure:

New Measure 12 = 
Var _table=FILTER(SUMMARIZE('Date','Date'[Year Month],'Date'[Month],"12",[Last Full 12 Months]),[12]<>BLANK())
RETURN
CALCULATE([Last Full 12 Months],FILTER(_table,[Month]=SELECTEDVALUE('Month table'[Month])))

New Measure 13-24 = 
Var _table=FILTER(SUMMARIZE('Date','Date'[Year Month],'Date'[Month],"13-24",[Last full 13-24 months]),[13-24]<>BLANK())
RETURN
CALCULATE([Last full 13-24 months],FILTER(_table,[Month]=SELECTEDVALUE('Month table'[Month])))

Is this the result you expected?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[danextian]

If by running total you mean YTD:

YTD = 
CALCULATE (
    [Sales],
    DATESYTD ( 'Date'[Date] ),
    REMOVEFILTERS ( 'Date' )
)

YTD LY = 
CALCULATE ( [YTD],  SAMEPERIODLASTYEAR ( 'Date'[Date] ) )

[Hammerhead]

Hi danextian,

Yes, I mean a running total. But not from January. I mean for the Last Full 12 Months.

So for today (16.12.2024) it would have to start from 01.12.2023 to 30.11.2024, and not from 01.01.2024 (or YTD).

[danextian]

I'm confused. The last 12 months measure in my initial response should do the trick as it iterates through the last 12 months starting from the current row month. Or do you mean YTD that starts November?

[Smalfly]

Hi @Hammerhead ,

Try this:

Accumulated Sales: Last Full 12 Months (€) =
VAR CurrentDate = MAX('Date'[Date]) -- Current date in the context
RETURN
CALCULATE(
[Sales: Last Full 12 Months (€)], -- Base measure for the last 12 months
FILTER(
ALL('Date'), -- Ignore filters on the Date table
'Date'[Date] <= CurrentDate && 'Date'[In Last Full 12 Months] = TRUE() -- Include only dates in the last 12 months up to the current date
)
)

[Hammerhead]

Thanks Smalfly, but this only starts from January onwards... and I need it to be the rolling last 12 full months.