Cumulative count 2 month average

[Anonymous]

Dear dax expert,

I have calculated the cumulative count of rejected packages with the following DAX: 

Cumulative Quantity = 
IF (
    MIN ( Espera[TimeFrame] )
        <= CALCULATE ( MAX ( Espera[TimeFrame] ); ALL ( Espera ) );
    CALCULATE (
        SUMX(Espera; [Afgekeurde flowpacks] );
        FILTER(
       ALLSELECTED(Espera);
            Espera[TimeFrame] <= MAX(Espera[TimeFrame]))))

Now I would like to create an additional measure for the cumulative count displaying the average cumulative count of the previous 2 months.

All help is welcome,

[v-yuta-msft]

Hi Luukv93,

No raw data have been given, so a general solution is like pattern below, please check if it can meet your requirement:

Average Value in Last Two Month =
AVERAGEX (
    FILTER (
        table,
        table[month] <= MAX ( table[month] )
            && table[month] >= EDATE ( MAX ( table[month] ), -2 )
    ),
    [Cumulative Quantity]
)

Regards,

Jimmy Tao

[Anonymous]

Hi @v-yuta-msft,

I am using the TimeFrame as a filter, this is a calculated colum with the formula:

TimeFrame = IF( Espera[Minute] = 5; CONCATENATE(Espera[Hour];",05"); CONCATENATE(CONCATENATE(Espera[Hour];",");Espera[Minute]))

The rejected flowpacks formula:

Afgekeurde flowpacks = 
COUNTX(Espera; Espera[Index])

Cumulative quantity formula:

Cumulative Quantity = 
IF (
    MIN ( Espera[TimeFrame] )
        <= CALCULATE ( MAX ( Espera[TimeFrame] ); ALL ( Espera ) );
    CALCULATE (
        SUMX(Espera; [Afgekeurde flowpacks] );
        FILTER(
       ALLSELECTED(Espera);
            Espera[TimeFrame] <= MAX(Espera[TimeFrame]))))

Your formula I modified to average last 10 days:

Average last 10D = 
AVERAGEX(
    FILTER(
        Datum;
        Datum[Date] <= MAX (Datum[Date] )
            && Datum[Date]>= EDATE( MAX( Datum[Date] ) ; -10 )
    );
    [Cumulative Quantity]
)

The outcomes show the rows do not show the right cumulative average of 10 days.

Sample data:

TimeFrame Afgekeurde flowpacks Cumulative Quantity Average last 10D
0,05 2 2 2
0,10 6 8 8
0,15 4 12 12
0,20 6 18 18
0,25 1 19 19
0,30 5 24 24
0,35 3 27 27
0,40 4 31 31
0,45 2 33 33
0,50 1 34 34
0,55 3 37 37

[Anonymous]

@v-yuta-msft Friendly reminder : )