Have Rolling Total, but making a mistake with Previous 12 month Rolling Total.

[mmace1]

Hi, 

So I wrote an expression that captures the rolling total for something - then it takes the filter context in my table, and calculates correctly.  The referenced measure {Distinct CustVen Paid] is just a DISTINTCOUNT on a column. 

Rolling Total Distinct Cust-Ven Paid =
CALCULATE (
    [Distinct CustVen Paid],
    FILTER (
        ALL ( Payment ),
        Payment[CreateDateUTC] <= MAX ( Payment[CreateDateUTC] )
    )
)

That works. 

But now...I also want to give the previous-12 month rolling total. I have some code, but checking my work - I'm doing something wrong!

Rolling Total Distinct Cust-Ven Paid Prev 12 Months =
CALCULATE (
    [Distinct CustVen Paid],
    FILTER (
        ALL ( Payment ),
        Payment[CreateDateUTC] <= MAX ( Payment[CreateDateUTC] )
            && Payment[CreateDateUTC]
                <= ( MAX ( Payment[CreateDateUTC] ) - 365 )
    )
)

Thoughts?  Thanks!

[Seward12533 · Accepted solution]

Few options - first for CUMULATIVE Functions your missing an ALL Term and you typically have have the ALL in the column as just  the column your filtering for your **bleep** and unless you have a date table  you will get inconstant results.

Rolling Total Distinct Cust-Ven Paid Prev 12 Months =
VAR MaxPayDate = MAX ( Payment[CreateDateUTC] RETURN
CALCULATE (
    [Distinct CustVen Paid],
    ALL(dates),
    FILTER (
        ALL ( date[Date] ),
        date[Date] <=  MaxPayDate)
            && date[Date] <= MaxPayDate - 365 )
    )
)

Also much easier with DATESINPERIOD function 🙂 Might work on your base table without dates but there can be issues if your missing dates etc...

Rolling Total Distinct Cust-Ven Paid Prev 12 Months =
VAR MaxPayDate = MAX ( Payment[CreateDateUTC] RETURN
CALCULATE (
    [Distinct CustVen Paid],
    DATESINPERIOD(Date Dates],MaxPayDAte-365  ,1 ,YEAR)
    )

[mmace1]

Hey...got the 2nd one working - that is much easier, thanks!

Rolling Total Distinct Cust-Ven Paid Prev 12 Months =
CALCULATE (
    [Distinct CustVen Paid],
    DATESINPERIOD (
        'Calendar Table'[Date],
        MAX ( Payment[CreateDateUTC] ) - 365,
        365,
        DAY
    )
)