cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mmace1 Member
Member

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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Seward12533 New Contributor
New Contributor

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

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 Smiley Happy 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)
    )
2 REPLIES 2
Highlighted
Seward12533 New Contributor
New Contributor

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

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 Smiley Happy 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 Member
Member

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

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