Average 12 months rolling without blanks

[OscarIvanCampos]

Hi all, 

I am looking for some assistance in DAX.  I need to calculate the 12 months rolling average by client base on the numbers of time they have purchased from us. 

I have come up with this formula that works ok for clients with consistent purchases throughout the 12 months window. But, the average gets off for clients who have only purchased once, twice, etc within those 12 months, as it divides the total number of invoices in 12 instead of doing only for the number of months that particular client purchased from us. 

I have attached the table showing what I mean. 

=
IF (
AND (
[# Invoices] > 0,
MAX ( Dates_Table[Date] )
> CALCULATE ( MIN ( Dates_Table[Date] ), ALL ( Dates_Table[Date] ) )
),
CALCULATE (
[# Invoices],
DATESINPERIOD ( Dates_Table[Date], LASTDATE ( Dates_Table[Date] ), -1, YEAR )
)
) /
CALCULATE([# Months],
FILTER( Dates_Table,
DATESINPERIOD (Dates_Table[Date], LASTDATE ( Dates_Table[Date] ), -1, YEAR )),
[# Invoices] > 0
)

Thanks in advance for your help. 

Oscar 

[Greg_Deckler · Accepted solution]

First, Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Second, See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...