Forum Discussion
Solved
Monthly Rolling Average
Hi everybody, I'm creating a monthly rolling average per type using data which has daily records. The DAX I created is not working for monthly rolling average. Below is what the data looks lik...
Hi markefrody
To use these kinds of calculations inefficient way you should add dates table to your model as a first step:Mark it a dates table:
and then modify your measure to:
rolling average rita =VAR NumOfMonths = 12VAR LastCurrentDate =MAX ( 'Dates'[Date] )VAR Period =DATESINPERIOD ( 'Dates'[Date], LastCurrentDate, - NumOfMonths, MONTH )VAR Result =CALCULATE (AVERAGEX(VALUES ( 'Dates'[Date Year/Month] ),[Count Sum]),Period)VAR FirstDateInPeriod = MINX ( Period, Dates[Date] )VAR LastDateWithSales = MAX ( 'data'[Date MM/DD/YYYY])RETURNIF ( FirstDateInPeriod <= LastDateWithSales, Result )exclude the name Rita 🙂more information about the importance of dates table :guide to create it :If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi markefrody
To use these kinds of calculations inefficient way you should add dates table to your model as a first step:
Mark it a dates table:
and then modify your measure to:
rolling average rita =
VAR NumOfMonths = 12
VAR LastCurrentDate =
MAX ( 'Dates'[Date] )
VAR Period =
DATESINPERIOD ( 'Dates'[Date], LastCurrentDate, - NumOfMonths, MONTH )
VAR Result =
CALCULATE (
AVERAGEX(
VALUES ( 'Dates'[Date Year/Month] ),
[Count Sum]
),
Period
)
VAR FirstDateInPeriod = MINX ( Period, Dates[Date] )
VAR LastDateWithSales = MAX ( 'data'[Date MM/DD/YYYY])
RETURN
IF ( FirstDateInPeriod <= LastDateWithSales, Result )
exclude the name Rita 🙂
more information about the importance of dates table :
guide to create it :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Ritaf1983
Hi Rita,
Thank you very much for your solution. Confirm it works. Thanks again. Really appreciate it.
Best regards,
Mark V
You're welcome ☺️