Dynamic Rolling Sum for last 12 months.

[Neo_007]

I need some help calculating the Rolling SUM 
Below is the sample data table simillar to orginal data.
This table (In dienst) has the following (relevant) columns:

1. Snapshot date - Monthly record 
2. Vendor ID 
3. Region 
4. Order: Status (1,0)
6. Return Order: Status (1,0).

Logic:
1. Rolling Sum Return 12 month = 

VAR Order-z= IF(ISBLANK(SUM(Data_File[Return Order])),0,SUM(Data_File[Return Order]))

VAR MovingSumresult = SUMX(

    FILTER(Data_File,

        Data_File[SNAPSHOT_DATE] >=  DATEADD(Data_File[SNAPSHOT_DATE].[Date],-11,MONTH) &&

      Data_File[SNAPSHOT_DATE] <= Data_File[SNAPSHOT_DATE])

        ),  Order-z)

RETURN MovingSumresult

Note: For trial, set logic for 2 months rolling, Yellow is actual value of month and Orange is Incorrect value of rolling.

Tableau Logic:
WINDOW_SUM(
SUM([Return Order])
, 0, 11)

2. Rolling Avg Order 12 month = 

VAR Order-z= IF(ISBLANK(AVERAGE(Data_File[Order Places])),0,AVERAGE(Data_File[Order Places]))

VAR MovingAvgresult = AVERAGEX(

    FILTER(Data_File,

        Data_File[SNAPSHOT_DATE] >=  DATEADD(Data_File[SNAPSHOT_DATE].[Date],-11,MONTH) &&

      Data_File[SNAPSHOT_DATE] <= Data_File[SNAPSHOT_DATE])

        ),  Order-z)

RETURN MovingAvgresult

Tableau Logic:
ROUND(
   WINDOW_SUM(
       ZN(SUM([Order Places]))
      , 0, 11)
   / 12 ,
0)

Final % Score = 1 - (Rolling Sum Return 12 month / Rolling Sum Order 12 month)

here is the power bi file for reference.
1st step is failing so final result is not matching.
Sample Data Power BI File: https://drive.google.com/file/d/1E8CvVUvxQ_DDDImqbwg9HkGXhjf2Tu8U/view?usp=sharing 

--------------------------------------------------------
In my original data file, 2 months rolling should be sum of last two months but insted of sum it present multiplication by 2. 
Logic used: 

Order rolling 2 =

SUMX(DATESINPERIOD(Order_count[SNAPSHOT_DATE],MAX(Order_count[SNAPSHOT_DATE]),-2,MONTH),SUM(Order_count[Return]))

[Neo_007]

Looking for solution of Rolling of 12 months 
At Nov = Sum of Value from Nov to Previous year Dec.
At Oct =  Sum of Value from Oct to Previous year Nov.
At Sept = Sum of Value from Sept to Previous year Oct.

[v-stephen-msft · Accepted solution]

Hi @Neo_007 ,

Thanks for reaching out.

You could try my forumula:

Return_MovingSum =
CALCULATE (
    SUM ( data[Return Order] ),
    FILTER (
        ALLSELECTED ( 'data' ),
        [Region] = MAX ( 'data'[Region] )
            && [SNAPSHOT_DATE] <= MAX ( 'data'[SNAPSHOT_DATE] )
            && [SNAPSHOT_DATE] > DATEADD ( 'data'[SNAPSHOT_DATE], -12, MONTH )
    )
)

When calculating the sum of months, we usually use the ALL or ALLSELECTED function to remove the month filter for the current row and customize the date range in the FILTER function. Here's the result.

And note that VAR doesn't work in your current formula environment, and I don't use VAR to define SUM (data[Return Order]) here.

Best Regards,

Stephen Tao

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

[Neo_007]

Thanks @v-stephen-msft  for replay, But I cant use Region in Calculation as Y axis will change based on parameter selection like Region, Category, Sub category, Product Type, Country...
And it fail for 2 months moving sum validation.
 
Validation logic by 2 months moving sum:
Return_MovingSum (2 months) = Current Month Return Total + Previous Month Return Total.