Rolling 12 months not working despite following online examples

[DAXisMyNemesis]

Hi all

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

[DAXisMyNemesis]

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

[Anonymous]

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