Moving Average - Last 3 days per category ID

[anil]

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.

Please help me with a solution.

PBIX LInk: https://www.dropbox.com/s/mtn2v0gr5il7kwx/MTD%20Test.pbix?dl=0

[v-yulgu-msft · Accepted solution]

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

[anil]

Thanks for the reply Yuliana Gu,

         It worked like a charm.

[anil]

Please help me with a solution.