Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've created the below code, the intention being to calculate a rolling number of customers who have had 1-4 Direct Debit failed payments in the last 12 months (I will have similar for 5-8 and 9+).
This works fine when I just use the value as a total in a card - however, if I apply a date filter or use the measure in a graph over time, the values calculated are restricted to failures within that month. What I want is to show, for Feb 2019, the number of customers who have accumulated 1 to 4 failures between 1st March 2018 and 28th Feb 2019.
Where am I going wrong? I've tried all manner of possible date filtering, and am getting the same result. I've tried using an ALL on the date table too, but I don't seem to be getting the desired effect so must be doing it wrong.
(I've seen something about using Integer keys to join Date dimensions to fact tables having an unexpected effect, but I thought that was limited to Power BI models - this is an SSAS 2017 cube my PBIX is calling, and I've read elsewhere that this doesn't apply as long as you specify the date field in the date table.)
Example figures (limited to 4 months data to save on processing time):-
1 to 4 DDs Failed
MonthName Failures (Wrong) Failures (Right)
2019-05 2129 2129
2019-06 2231 3352
2019-07 1990 4290
2019-08 578 4596
TOTAL 4596 4596
Code:-
Accounts with 1-4 Direct Debit Fails Last 12 Months:= CALCULATE ( COUNTROWS( FILTER( SUMMARIZE( TRANSACTIONS, DIMAccounts[Key], "DD Fails", COUNTROWS(TRANSACTIONS) ) , [DD Fails] >=1 && [DD Fails] <=4 ) ) , DIMTransactionTypes[PaymentMethodGroup]="Direct Debit" , USERELATIONSHIP(DIMDate[DateKey], TRANSACTIONS[KeyDatePeriodEnd]) , DATESBETWEEN ( DIMDate[DateValue] , NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( DIMDate[DateValue] ) ) ) , LASTDATE ( DIMDate[DateValue] ) ) )
Any help greatly appreciated!!
Thanks
-- base measure
[# Transactions] = COUNTROWS ( TRANSACTIONS )
-- your measure
Accounts with 1-4 Direct Debit Fails Last 12 Months :=
var __lastVisibleDate = LASTDATE ( DIMDate[DateValue] )
var __startDateFor12MRollingPeriod =
MAX(
NEXTDAY( SAMEPERIODLASTYEAR( __lastVisibleDate ) ),
SAMEPERIODLASTYEAR( NEXTDAY( __lastVisibleDate ) )
)
var __12MRollingPeriod =
DATESBETWEEN (
DIMDate[DateValue],
__startDateFor12MRollingPeriod,
__lastVisibleDate
)
var __12MRollingPeriodIsContainedInCalendar =
NOT ISEMPTY( __startDateFor12MRollingPeriod )
var __relevantAccounts =
CALCULATETABLE (
FILTER (
ADDCOLUMNS(
-- SUMMARIZE should never, ever, ever
-- be used to calculate numbers. It can
-- only be used to do grouping. Instead,
-- use the ADDCOLUMNS/SUMMARIZE pattern.
SUMMARIZE (
TRANSACTIONS,
DIMAccounts[Key]
),
"DD Fails", [# Transactions]
),
1 <= [DD Fails] && [DD Fails] <= 4
),
__12MRollingPeriod,
DIMTransactionTypes[PaymentMethodGroup] = "Direct Debit",
USERELATIONSHIP ( DIMDate[DateKey], TRANSACTIONS[KeyDatePeriodEnd] )
)
var __result = COUNTROWS( __relevantAccounts )
return
if (
__12MRollingPeriodIsContainedInCalendar,
__result
)
Your DIMDate table must be marked as a Date table and the Date column, which stores real dates, must be chosen as the row unique identifier.
Best
Darek
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
43 | |
23 | |
21 | |
15 | |
15 |
User | Count |
---|---|
45 | |
31 | |
30 | |
18 | |
17 |