Rolling cumulative TOTAL with 2 calendars

[BI_Samurai]

Hello,

I would like to calculate correctly a total of a sales measure to show a rolling cumulative total for the last 12 months.

This measure is intented to work with 2 calendars - first main calendar is used to select data in the slicer and second calendar is used to display data on the visual only until the selected month from the first calendar and slicer.

The goal is to display the data only until the time period selected in the year/month slicers meaning that if I select April 2024 I should see the total result calculating May 2023 up to April 2024. For a single month I get correct values, however the totals at the moment return only data relevant to 2024.

This is the measure:

Sales Rolling test =

var var_end =

MAX('📆Calendar 2'[Date])

var var_start = EOMONTH(var_end,-12)

var var_result =

IF(SELECTEDVALUE('📆Calendar 2'[Month #])>SELECTEDVALUE('📆Calendar'[Month #]),

blank(),

CALCULATE(SUM(Sales[Total Sales]),

    REMOVEFILTERS('📆Calendar'),

 KEEPFILTERS('📆Calendar'[Date] > var_start &&'📆Calendar'[Date]<=var_end)))

RETURN

var_result

Do you know what needs to be added to my measure in order to solve this?

Any other ideas how to fix this?

Thank you.

[Joe_Barry]

Hi @BI_Samurai 

Create an Inactive relationship between Calendar 2 and Calendar on the Date columns. Create a measure similar to below. Add the Date columns in the visual from Calendar2 and the Slicer data should come from Calendar.

Sales P12M = 
VAR RefDate =
    MAX ( Calendar[Date] )
VAR PrevDate =
    DATESINPERIOD ( Calendar2[Date], RefDate, -12, MONTH )
RETURN
    CALCULATE (
        [Sales - Last 12 Months],
        REMOVEFILTERS ( Calendar ),
        KEEPFILTERS ( PrevDate ),
        USERELATIONSHIP ( Calendar[Date], Calendar2[Date] )
    )

Hope this helps

Joe

If you found my answer helpful and it solved your issue, please accept as solution

[BI_Samurai]

Awesome, thank you !

[BI_Samurai]

Hello,

thanks for a quick reply and the tip. What is the exact measure that you use here [Sales - Last 12 Months] ?

thank you

[Joe_Barry · Accepted solution]

It's a rolling 12 Month Measure. 

Sales - Last 12 Months = 
CALCULATE (
    [Sales],
    DATESINPERIOD ( Calendar[Date], MAX ( Calendar[Date] ), -12, MONTH )
)

Sales is a simple Sum

SUM(Sales[Amount])