Retained Deposits DAX

[tomhopewell]

Hi all,

I am trying to calculated all of our "Retained First Time Deposits" which are basically all customers who made their first deposit in month 0 and then went on to make another deposit in month 1.

I currently have this...

Retained FTDs = COUNTROWS (

FILTER (

ADDCOLUMNS (

VALUES ( 'Fact Transactions'[PlayerKey] ),

"LastMonthTotal", CALCULATE (

COUNTROWS ('Fact Transactions'),

FILTER (

ALL ('Dim Date'),

'Dim Date'[MonthIndex]=MIN('Dim Date'[MonthIndex])-1

)

),

"PreviousMonthTotal", CALCULATE (

COUNTROWS ('Fact Transactions'),

FILTER (

ALL ('Dim Date'),

'Dim Date'[MonthIndex]=MIN('Dim Date'[MonthIndex])-2),'Fact Transactions'[isInitialDeposit] = TRUE)

),NOT(ISBLANK([LastMonthTotal])) && NOT(ISBLANK([PreviousMonthTotal]))))

Note:

MonthIndex  = Running number for each month

Currently, the numbers it is returning are lower than they should be and i'm unsure as to where it's going wrong. Unfortunately, due to the sensitive nature of the data, I can't provide any samples.

I am new to DAX so any help would be greatly appreciated.

Thanks in advance,

Tom

[v-chuncz-msft · Accepted solution]

@tomhopewell ,

You may try using INTERSECT.

https://community.powerbi.com/t5/Desktop/Want-to-calculate-number-of-new-customers-per-every-month-o...