cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anil Member
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.

 S1.JPG

 

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.s2.JPG

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
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
        )
    )

1.PNG

 

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
anil Member
Member

Re: Moving Average - Last 3 days per category ID

Please help me with a solution.

Community Support Team
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
        )
    )

1.PNG

 

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.
anil Member
Member

Re: Moving Average - Last 3 days per category ID

Thanks for the reply Yuliana Gu,

         It worked like a charm.Smiley Happy