Missing months from DAX expression for a Measure

Anonymous — Fri, 22 Sep 2023 15:55:58 GMT

I have written this DAX expression for a measure in order to find the 12 month rolling average starting from the most recent month to 12 months before.

12MonthRollingAvg =

VAR numOfMonths=12

VAR lastCurrentDate=MAX('Calendar_Month'[First Day of Month])

VAR Period = DATESINPERIOD('Calendar_Month'[First Day of Month], lastCurrentDate,-numOfMonths,MONTH)

VAR result = CALCULATE(

AVERAGEX(

VALUES('Calendar_Month'[Calendar Month - Name]), [Measure_2]),

Period

)

Return

if ([Measure_2]>0 , result)

I have data from 2018 to 2023 coming from an SSAS connection. The visualization with this measure is posted below:

This is the most recent month in the table i.e. September.
However the first month displays February whereas it should have been October i.e. October to September.

I'm unsure as to why this is happening as the data is precise for all months from 2018-2023. Would appreciate any help.

Re: Missing months from DAX expression for a Measure

some_bih — Sun, 24 Sep 2023 13:49:58 GMT

Hi Anonymous I would say your measure for Average do not include unique values for periods as in 12 months period there is "2 years" included, like 3 months from previous year and 9 months in this year. I mean, you should include your Calendar date, columns like YEAR-MONTH, not just month. Try it and debug.

Great link with example is below

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

Hope this help. Cudos appreciated.

topic Missing months from DAX expression for a Measure in DAX Commands and Tips

Missing months from DAX expression for a Measure

Re: Missing months from DAX expression for a Measure