Last 6 months rolling average DAX measure

[Thamizh_hfhs]

Hi Everyone. I have a measure which calculates the last 6 months rolling average of CMAED as follows,

Last 6 Months CMAED Average = DIVIDE(CALCULATE('FactGeneralLedger'[CMAEDCDSMonthlytotal], DATESINPERIOD('DimDate'[DateValue], MAX(DimDate[DateValue]), -6, MONTH)), 6)   

I have a "MonthYear" filter and whenever I select a MonthYear, for example September2022, the above measure calculates the average from April to September. But, I wanted the average from March to August. How do I alter the above measure  to get the desired result? Thanks in advance.

Please see sample below. For the September month,

The current measure gives the last 6 months average as 2,375 (April to September)
But I need the average to be 2,125 (March to August)
How do I alter my measure to get the desired result ?

Month Year	CMAED
01-2022	1,000
02-2022	1,250
03-2022	1,500
04-2022	1,750
05-2022	2,000
06-2022	2,250
07-2022	2,500
08-2022	2,750
09-2022	3,000

[amitchandak]

@Thamizh_hfhs , Try a  measure like

6 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[CMAED])))
,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

[Thamizh_hfhs]

Last 6 months CMAED Test = CALCULATE(AVERAGEX(VALUES('DimDate'[MonthYear]), CALCULATE('FactGeneralLedger'[CMAEDCDSMonthlyTotal])), DATESINPERIOD('DimDate'[DateValue], MAX('DimDate'[DateValue]), -6, MONTH))

I tried and it gives the same result i.e; 2,375 (April to September). Doesn't seem to be working unfortunately.

[v-cgao-msft · Accepted solution]

Hi @Thamizh_hfhs ,

Please try:

Measure 2 = 
VAR _YEAR = MAX('DimDate'[Year])
VAR _MONTH = MAX('DimDate'[Month])
VAR _PRE_MONTH = IF(_MONTH=1,12,_MONTH-1)
VAR _PRE_YEAR = IF(_MONTH=1,_YEAR-1,_YEAR)
VAR _DATE = CALCULATE(MAX('DimDate'[DateValue]),FILTER(ALL('DimDate'),'DimDate'[Year]=_PRE_YEAR&&'DimDate'[Month]=_PRE_MONTH))
VAR _RESULT = DIVIDE(CALCULATE(SUM('FactGeneralLedger'[CMAEDCDSMonthlytotal]),DATESINPERIOD('DimDate'[DateValue],_DATE,-6, MONTH)),6)
RETURN
_RESULT

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data