Create a Rolling 3 month sum within a Rolling X Month calculation

[Anonymous]

Hey guys, this is a step beyond my last question so I thought I'd create a new post.  

My last post was:  Rolling X Month Trend Line

So thanks to the article that was in that post I was able to get this to work with simple sums.  IE:  Sum of Sales.  

However, my needs vary witht his and one of those needs is to create a more complex calculation.  I want to show what is essentially a sales turnover calculation that has rolling 3 month averages/sums within it.  

So My calculation would be something like this:  (Rolling 3 Month SUM COGS)/ (3 Month Inventory Avg)

Currently my issue is I can't get either the numerator or denominator to show enough data.  My calculation will only show a couple months of data, and not do a rolling sum.  Below is my attempt at just getting a numerator to work:

Rolling X Monthly  % (last n months) = 
VAR MaxFactDate =
    CALCULATE ( MAX ( Date[MeasureDate] ), ALL (  'DIMCalendar'  ) ) -- ignore the selected date filter, and find the max of date in Sales table 
VAR FDate =
    ENDOFMONTH ( 'DIMCalendar'[DateKey] ) -- get the last day of the month selected in the date filter
VAR Edate =
    EDATE ( FDate, -[N Value] ) -- get the last day of -N months
    
VAR  3MonthRolling = CALCULATE (
    'FACT'[Total COGS],
    DATESINPERIOD (
        'DIMCalendar'[DateKey],
        LASTDATE (  ('DIMCalendar'[DateKey] ) ),
        -3,
        MONTH
    )
)
VAR NUMERATOR = ( 3MonthRolling*10/2)

	
RETURN
    IF (
MaxFactDate <= MAX ( 'DIMCalendar'[DateKey] )
&& MaxFactDate > Edate,
   CALCULATE ( IF (  ( NUMERATOR) = BLANK (), 0, ( NUMERATOR)) , ALL (  'DIMCalendar'  ) )
    )

[v-lili6-msft]

hi, @Anonymous

Please share your sample pbix or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading. it will be a better understanding of your requirement.

Best Regards,

Lin

[Anonymous · Accepted solution]

Thanks for the reply but I actually found another post that matched what I wanted to do and the solution for it fixed my problem.

Average 3 Month Question on Power BI