cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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
Highlighted
Resolver I
Resolver I

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

View solution in original post

2 REPLIES 2
Highlighted
Community Support
Community Support

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

hi, @ISUGraber

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

 

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.
Highlighted
Resolver I
Resolver I

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

View solution in original post

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors