Reply
Frequent Visitor
Posts: 5
Registered: ‎08-27-2018
Accepted Solution

Rolling average by day for P3M

Hey guys! 

 

I have a question about calculating rolling average for P3M 

 

Here is the small example of my dataset:

 

delete2.PNG

 

As you can see here, I have shipments to the same customer at the same date with different sales amount. 

And the result I want to get is attached below. The problem is that i need to calculate all sales for the period and then to calculate average for the P3M period and I dont know how to do it

Thank you guys! 

delete3.png


Accepted Solutions
Highlighted
Community Support Team
Posts: 5,689
Registered: ‎09-21-2016

Re: Rolling average by day for P3M

Hi @em6jq65,

 

Please try this measure:

RollingAVR =
CALCULATE (
    SUM ( Table[Amount(pieces)] ),
    FILTER (
        ALL ( Table ),
        Table[Date].[Year] = YEAR ( MAX ( Table[Date] ) )
            && Table[Date].[MonthNo] <= MONTH ( MAX ( Table[Date] ) )
            && Table[Date].[MonthNo]
                >= MONTH ( MAX ( Table[Date] ) ) - 2
    )
)
    / 3

 

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.

View solution in original post


All Replies
Highlighted
Community Support Team
Posts: 5,689
Registered: ‎09-21-2016

Re: Rolling average by day for P3M

Hi @em6jq65,

 

Please try this measure:

RollingAVR =
CALCULATE (
    SUM ( Table[Amount(pieces)] ),
    FILTER (
        ALL ( Table ),
        Table[Date].[Year] = YEAR ( MAX ( Table[Date] ) )
            && Table[Date].[MonthNo] <= MONTH ( MAX ( Table[Date] ) )
            && Table[Date].[MonthNo]
                >= MONTH ( MAX ( Table[Date] ) ) - 2
    )
)
    / 3

 

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.