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' ) ) )
Solved! Go to Solution.
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.