cancel
Showing results for
Did you mean:
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
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 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
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 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)
)```
Highlighted
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
)
)```