cancel
Showing results 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.

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,

3 REPLIES 3
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]
)```

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

Announcements

#### 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

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors