Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Cumulative count 2 month average

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,

 

 

 

 

Need Help

3 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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