How to get the moving average of the biggest two among many?

[FreemanZ]

As mentioned, for the sample data below:

ReportDate	Sales
1/1/2022	61
1/2/2022	86
1/3/2022	64
1/4/2022	69
1/5/2022	62
1/6/2022	96
1/7/2022	64
1/8/2022	60
1/9/2022	71
1/10/2022	79
1/11/2022	69
1/12/2022	73

The requirement is to get the average sales of the biggest two month in the last six months. 

For example, for 1/12/2022, the target value shall be 76  or (79+73)/2,  for 1/11/2022, it shall be 87.5 or (96+79)/2. 

 

I am struggling on how to filter the 2 out of the 6. 

As it is part of other calculation, realization with pure DAX code is welcome. 

[FreemanZ]

It works well. Thank you Jihwan.

[Jihwan_Kim · Accepted solution]

Hi,

In case you do not have a calendar table, please try below.

Please check the below picture and the attached pbix file.

Expected result measure: =
VAR _currentmonthend =
    EOMONTH ( MAX ( Data[ReportDate] ), 0 )
VAR _sixmonthboforestart =
    EOMONTH ( MAX ( Data[ReportDate] ), -6 ) + 1
VAR _sixmonthsperiodtable =
    CALCULATETABLE (
        Data,
        Data[ReportDate] >= _sixmonthboforestart,
        Data[ReportDate] <= _currentmonthend
    )
VAR _biggesttwovaluesavg =
    AVERAGEX ( TOPN ( 2, _sixmonthsperiodtable, Data[Sales], DESC ), Data[Sales] )
RETURN
    _biggesttwovaluesavg