cancel
Showing results for
Did you mean:
Regular Visitor

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

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'  ) )
)```
1 ACCEPTED SOLUTION

Accepted Solutions
Regular Visitor

## Re: Create a Rolling 3 month sum within a Rolling X Month calculation

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

2 REPLIES 2
Community Support Team

## Re: Create a Rolling 3 month sum within a Rolling X Month calculation

hi, @ISUGraber

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Regular Visitor

## Re: Create a Rolling 3 month sum within a Rolling X Month calculation

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