Forum Discussion
Rolling Average from Summed Measure
I'm working on a report that tracks conversion by month with the ability for an executive to manually input a conversion override that would be applied quarterly. I have that manual input set up via an excel sheet and then I determine which months to apply to by linking back to my date table.
The trouble I'm running into is I then have to perform a 6m rolling average on the conversion and it's not working when I integrate the applied conversion. To get the applied conversion I'm just summing each override quarter as there is no time overlap. Below is the table for reference along with my current 6m rolling average formula. Any help would be greatly appreciated!
Conversion 6m Rolling =
if([Applied Conversion]>0,
CALCULATE([Applied Conversion],
DATESINPERIOD(DateTable[Date],
LASTDATE(PREVIOUSMONTH(DateTable[Date])),-6,MONTH))
,
CALCULATE([Conversion],
DATESINPERIOD(DateTable[Date],
LASTDATE(PREVIOUSMONTH(DateTable[Date])),-6,MONTH)))
3 Replies
- parry2k
Super User
Anonymous check these links and sure will help
https://powerpivotpro.com/2013/07/moving-averages-sums-etc/
https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/
v-lili6-msftCommunity Support
hi, Anonymous
You may try to use New quick measure to create a rolling 6 months average as below:
This is more convenient and simple.
for your case, if you could try to add a measure like
measure = if([Applied Conversion]>0 , [Applied Conversion] , [Conversion])
then use it to create a New quick measure as above
If not your case, please share your sample pbix or some data sample and the formula and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
