cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ISUGraber Regular Visitor
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
ISUGraber Regular Visitor
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
Community Support Team

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.
ISUGraber Regular Visitor
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