cancel
Showing results for
Did you mean:
Member

## Moving Average - Last 3 days per category ID

Hi all,

I have a strange requirement. My dataset consists of orders. I am trying to calculate average MTD per ID for last 3 days.

I have calculated MTD Average per ID per month by. First I have calculated running total of the discount.

```Rolling_Discount =
VAR EarliestDate = CALCULATE(MIN('Sample'[Order Date]),ALLSELECTED())
RETURN CALCULATE(sum('Sample'[Discount%]),FILTER(ALL('Sample'[Order Date]),'Sample'[Order Date] <= MAX('Sample'[Order Date]) && 'Sample'[Order Date] >= EarliestDate))```

Then calculated rolling count of the orders per day.

```Cumulative_Count =
VAR EarliestDate = CALCULATE(MIN('Sample'[Order Date]),ALLSELECTED())
RETURN CALCULATE(COUNT('Sample'[ordernumber]),FILTER(ALL('Sample'[Order Date]),'Sample'[Order Date] <= MAX('Sample'[Order Date]) && 'Sample'[Order Date] >= EarliestDate))```

Then divided both to get MTD Average.

`Mtd Test = DIVIDE([Rolling_Discount],[Cumulative_Count])`

Everything is working fine.

But I want to show only last 3 days per month in the report. So if I filter date to top 3 on selected month my calculation is computing for the filtered last 3 days.

But what I want to show is the MTD averages from the start of the month, but in the report display only last 3 days of the month.

In the above image I have filtered last 3 days. Its calculating average for the filtered 3 days. But the result should be 47.62 , 47.22, 46.74 for the last 3 days.

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Moving Average - Last 3 days per category ID

Hi @anil,

Please make some modofication to measures [Cumulative_Average] and [Cumulative_Count] as below:

```Cumulative_Average =
VAR EarliestDate =
CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) )
RETURN
CALCULATE (
SUM ( 'Sample'[Discount%] ),
FILTER (
ALL ( 'Sample'[Order Date] ),
'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] )
&& 'Sample'[Order Date] >= EarliestDate
)
)

Cumulative_Count =
VAR EarliestDate =
CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) )
RETURN
CALCULATE (
COUNT ( 'Sample'[ordernumber] ),
FILTER (
ALL ( 'Sample'[Order Date] ),
'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] )
&& 'Sample'[Order Date] >= EarliestDate
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Member

## Re: Moving Average - Last 3 days per category ID

Community Support Team

## Re: Moving Average - Last 3 days per category ID

Hi @anil,

Please make some modofication to measures [Cumulative_Average] and [Cumulative_Count] as below:

```Cumulative_Average =
VAR EarliestDate =
CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) )
RETURN
CALCULATE (
SUM ( 'Sample'[Discount%] ),
FILTER (
ALL ( 'Sample'[Order Date] ),
'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] )
&& 'Sample'[Order Date] >= EarliestDate
)
)

Cumulative_Count =
VAR EarliestDate =
CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) )
RETURN
CALCULATE (
COUNT ( 'Sample'[ordernumber] ),
FILTER (
ALL ( 'Sample'[Order Date] ),
'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] )
&& 'Sample'[Order Date] >= EarliestDate
)
)```

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Member

## Re: Moving Average - Last 3 days per category ID

Thanks for the reply Yuliana Gu,

It worked like a charm.