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 matrix table.  I need the rolling 6 months to count back from the month prior to today, and keep the ability to average by the count of labels, some accounts may have less then 6 months of data.

My data:

Invoice date      Invoice Total

10/14/2021 $ 2,175.02
11/19/2021 $ 2,909.67
12/20/2021 $ 2,158.40
1/27/2022 $ 2,906.29
2/16/2022 $ 2,068.96
3/24/2022 $ 2,097.71
4/15/2022 $ 2,165.85

 

Current measure result is $2,384.48 the average of November to April,  I need the measure result to be $2,386.01 the average of October to March.

 

Current Measure:

 

Moving AverageX =

calculate(

    DimMeasures[Invoice Total],

        DATESINPERIOD('Calendar'[Date],lastdate('Calendar'[Date]),-5,MONTH))  

    /(

Calculate(

    DISTINCTCOUNT(BillingPeriod[Label]),

        DATESINPERIOD('Calendar'[Date], lastdate('Calendar'[Date]),-5,MONTH)))

 

There is an existing relationship between Calendar and Invoice Batches which is the DimMeasures-Invoice Total.

 

I've read tons of posts on this but have not been able to get any of the answers provided to work.  Please let me know if there is any additional data is needed.  I would really appreciate the assistance.

  • 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))

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    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.

       

  • mcaldwell's avatar
    mcaldwell
    Frequent Visitor
    4 years ago

    With a minor tweek I was able to get one of amitchandak suggestions to work.  Here is where I landed.

     
    Moving AverageX =
    CALCULATE(sum(invoice_metrics[Correct_FTDue]),DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH))
    /(
    Calculate(
    DISTINCTCOUNT(BillingPeriod[Label]),
    DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH)))

     

     

    Moving AverageX =
    CALCULATE(sum(invoice_metrics[Correct_FTDue]),DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH))
    /(
    Calculate(
    DISTINCTCOUNT(BillingPeriod[Label]),
    DATESINPERIOD('Calendar'[Date],eomonth(MAX('Calendar'[Date]),-1),-5,MONTH)))
     
    Thank you!! I'm going to get lots of Diet Coke for this, while I can't share the pop, I will make sure you get most of the credit!