Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Anonymous
Not applicable

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,

 

tempsnip.png

 

 

 

3 REPLIES 3
v-yuta-msft
Community Support
Community Support

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
Not applicable

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]
)

tempsnip.png

 

 

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
Not applicable

@v-yuta-msft Friendly reminder : )

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors