Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 |
Solved! Go to 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
@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))
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
User | Count |
---|---|
87 | |
84 | |
67 | |
63 | |
63 |
User | Count |
---|---|
208 | |
121 | |
112 | |
79 | |
71 |