3-Month average of a measure

[Mosquito87]

Hi all,

I already read a couple of similar threads and spent hours on my own but still not found a suitable DAX solution that works for my problem.

I am trying to create a measure [_Ramp-Up_AVG] that calculates a 3m-average based on the distinct monthly values of measure [_Ramp-Up-Month] which is itself a measure that is calculated independently. The measures are calculated as follows:

_Total_Claims = CALCULATE(SUM('Field Data'[Quantity:Pareto 1]))

_Sales_Cumulated = 

CALCULATE(

    [_CNTRY_TOTALSALES],

    FILTER(

        ALL('DimVisualDates'[VIS_Date]),

        ISONORAFTER('DimVisualDates'[VIS_Date], MAX('DimVisualDates'[VIS_Date]), DESC)))

_RampUp_Month = CALCULATE(([_Total_Claims]*12)/[_Sales_Cumulated]*100) --> works fine

The following table shows the intended results.

Monat	Sales	[_Sales_Cumulated]	[_Total_Claims]	[_Ramp-Up-Month]	[_Ramp-Up-AVG]
Jan 21	203	203	1	5,91%
Feb 21	167	370	0	0,00%
Mrz 21	205	575	3	6,26%	4,06%
Apr 21	204	779	3	4,62%	3,63%
Mai 21	363	1142	0	0,00%	3,63%
Jun 21	396	1538	1	0,78%	1,80%
Jul 21	301	1839	12	7,83%	2,87%
Aug 21	611	2450	2	0,98%	3,20%
Sep 21	117	2567	9	4,21%	4,34%
Okt 21	320	2887	2	0,83%	2,01%
Nov 21	255	3142	0	0,00%	1,68%
Dez 21	277	3419	1	0,35%	0,39%

I am having trouble with calculating the correct 3-month average now.

My first approach was to build up [_Ramp-Up_AVG] as follows:

_RampUp_AVG = DIVIDE(calculate('Field Data'[_RampUp_Month],DATESINPERIOD(DimVisualDates[VIS_Date],ENDOFMONTH(DimVisualDates[VIS_Date]),-3,MONTH)),3,BLANK())

Unfortunately it delivers false results (that do not match with the correct values, see table above).

I really appreciate your support. 

Best regards

[Anonymous · Accepted solution]

Hi @Mosquito87 ,

You can update the formula of measure [_RampUp_AVG] as below and check whether it can return the correct data in your scenario:

_RampUp_AVG = 
VAR _LastDate =
    LASTDATE ( DimVisualDates[VIS_Date] )
RETURN
    CALCULATE (
        AVERAGEX ( VALUES ( DimVisualDates[Monat] ), [_RampUp_Month] ),
        FILTER (
            ALL ( DimVisualDates ),
            DimVisualDates[VIS_Date] <= _LastDate
                && DimVisualDates[VIS_Date] > DATEADD ( _LastDate, -3, MONTH )
        )
    )

Best Regards