How to not have a Rolling 12 be affected by relative date filter?

[KathleenW]

I have a graph that I want to show the rolling 12 sum of number of sales over the past year.

I want to have it show a years worth of data but I want it to calculate since before the date filter. It WAS working, and looked like this:

But now when I filter down to Past 365 Days, it looks like this:

It just increases from whenever the start of the graph is rather than calculating from "before" the graph. I have multiple years worth of data before this year. Not sure why it's doing this. The only filter I have on is a Relative date filter.

12MonthRolling = CALCULATE (

[Number Sales],

    DATESINPERIOD('Date Table'[Date],LASTDATE('Date Table'[Date]), -365,DAY)

)

[v-jianpeng-msft · Accepted solution]

Hi, @KathleenW 

Here's how you calculate your total sales for the past 12 months. I created the following sample data:

First, I need to generate a continuous date table based on the date column of the sample data, I use the following DAX:

Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))
Month = MONTH('Date'[Date]) 
Year = YEAR('Date'[Date])

Next we need to connect this date table with our data table:

We now begin our rolling sales calculations over the past year. First create a measure to calculate how many months have passed.

Month pass =
COUNTROWS (
    SUMMARIZE (
        CALCULATETABLE (
            'Date',
            DATESBETWEEN (
                'Date'[Date],
                MINX ( ALL ( 'Date'[Date] ), 'Date'[Date] ),
                MAX ( 'Date'[Date] )
            )
        ),
        'Date'[Year],
        'Date'[Month]
    )
)

Calculate rolling sales over the past 12 months via the following DAX:

12MonthRolling =
IF (
    [Month pass] >= 12,
    CALCULATE (
        [Number sales],
        DATESBETWEEN (
            'Date'[Date],
            EDATE ( MIN ( 'Date'[Date] ), -12 ),
            MAX ( 'Date'[Date] )
        )
    ),
    BLANK ()
)

The calculation results are as follows:

This will allow you to correctly display cumulative sales starting from the previous data. I have provided the PBIX file used this time below. If it can help you, that would be great. The link below is a case with the same problem as yours.

Solved: Re: Rolling 12 month total - Microsoft Fabric Community

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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