Forum Discussion

em6jq65's avatar
em6jq65
Frequent Visitor
8 years ago
Solved

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:

 

 

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! 

  • v-yulgu-msft's avatar
    v-yulgu-msft
    7 years ago

    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

1 Reply

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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