Rolling Average needs to exclude current month

[mcaldwell]

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.

[mcaldwell]

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!

[mcaldwell]

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]

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.

[amitchandak · Accepted solution]

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