Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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) )
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) )
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 ) )
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |