Dax formula - calculate 6 rolling month back average

2
votes

I have a table named [Tasks] which is linked to a date table named [Dimdate]. They are linked with the date of reference of a tasks [DC_date]. The objective is to have a measure that can calculate the average on in interval of 6 month before the specific date.

For example: if I have a date in 2020 August, the formula will calculate the average from March 2020 to 2020 August.

Here is actually the Dax formula that I have integrated on visual studio but still doesn’t work :

CALCULATE(CALCULATE( [average] ;FILTER( 
[TASKS]; 
DATESINPERIOD(Dimdate[Date];MAX(Dimdate[Date]);-6;Month)));
CROSSFILTER(TASKS[Dc_Date];Dimdate[Date];None)) ```
1
datetimepowerbidaxdaxstudio

1 Answers

0
votes

The relationship on TASKS[Dc_Date] should not be removed, otherwise the Time Intelligence function would not work. Unless there are some other existing filters to be removed, this code should be enough

CALCULATE(
    [average];
    DATESINPERIOD(
        Dimdate[Date];
        MAX( Dimdate[Date] );
        -6;
        MONTH
    )
)