Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mmace1
Impactful Individual
Impactful Individual

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
Seward12533
Solution Sage
Solution Sage

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

View solution in original post

2 REPLIES 2
Seward12533
Solution Sage
Solution Sage

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
Impactful Individual
Impactful Individual

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.