Forum Discussion

mcaldwell's avatar
mcaldwell
Frequent Visitor
4 years ago
Solved

Rolling Average needs to exclude current month

Hello, I have the following measure that is calculating a rolling 6 month average however it includes any current month data and if an account does not have any current data it does not appear in my...
  • amitchandak's avatar
    amitchandak
    4 years ago

    mcaldwell , Try like

     

    Rolling 5 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-5,MONTH))

     

    Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-6,MONTH))

mcaldwell's avatar
mcaldwell
Frequent Visitor
4 years ago

Thank you amitchandak for the reply, unfortunately I still couldn't get that to work.  Further discussion today from "experts" working on this says we won't be able to get it to work since we have wonky billing periods and we cannot get the calendar to just look back to prior month.

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi mcaldwell ,

     

    What do you mean by "since we have wonky billing periods and we cannot get the calendar to just look back to prior month"?

     

    If you have a full calendar table, you could try using the time intelligence function.

     

    Ave_Invoice_TimeIntelligence = 
AVERAGEX (
    DATESINPERIOD (
        'Calendar'[Date],
        EOMONTH ( MAX ( 'Calendar'[Date] ), -1 ),
        -6,
        MONTH
    ),
    [Invoice Total]
)


    If you can't get the correct value, maybe you can try to limit the date range directly.

     

    Ave_Invoice = 
VAR pre_month_end =
    EOMONTH ( MAX ( 'Calendar'[Date] ), -1 )
RETURN
    AVERAGEX (
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[Date] <= pre_month_end
                && 'Calendar'[Date] > EOMONTH ( pre_month_end, -6 )
        ),
        [Invoice Total]
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.