New/Dropped Accounts Logic Issue

DAX DAX Calculations
1

Hi Guys,

I am trying to created a report which shows new and dropped accounts, the user requested that they would also like to see in which age buckets these new/dropped account are in. An age bucket is define by the account maturity date less the previous business day. I created this age logic on power query as a calculated column. Then I created another column with the buckets as follows:

Buckets = SWITCH(TRUE(),Age[Value] < 91,“0m - 3m”
,Age[Value] < 182,“3m - 6m”
,Age[Value] < 365,“6m - 12m”
,Age[Value] < 730,“1y - 2y”
,Age[Value] < 1095,“2y - 3y”
,Age[Value] < 1825,“3y - 5y”
,Age[Value] <= 3650,“5y - 7y”
,Age[Value] > 3650,“Over 10y”
,“N/A”)

I then created a measure that takes compares two dates DTC = Current Date and DTP = Previous Date, which are selected by a user via a filter, then does an EXCEPT using the transaction IDs between current date and previous date.

#TxnInCurrNotInPrev = VAR dC = [#DtC]
VAR dP = [#DtP]
RETURN
COUNTROWS (
EXCEPT (
CALCULATETABLE (
VALUES ( ‘TableName’[TXN_ID] ),
‘Date’[BusinessDate] = dC
),
(
CALCULATETABLE (
VALUES ( ‘TableName’[TXN_ID] ),
‘Date’[BusinessDate] = dP
)
)
)
)

This works perfectly however when I add the Age Bucketing into the Matrix I also see values for transactions that have moved between buckets through maturity i.e. moved from being 90 days old (in the 0-3m bucket) to being 91 days old (in the 3m - 6m bucket) and the users actually want to see only the truly new customers i.e. Didnt exist at all previously but now exists in the current date’s dataset.

IN the attached screengrab you will see that at a Business Entity level we have a correct new customer count of 1 customer but if I add a drilldown of the age buckets i get 3,1,2,2 in various buckets.

Matrix Output
Matrix Output1428×322 126 KB

Can you please assist.

2

Hi @Lazola, we aim to consistently improve the topics being posted on the forum.

I noticed you didn’t provide a PBIX file. Providing one will help users and experts find a solution to your inquiry faster and better.

A perfect initial question includes all of the following:

  • A clear explanation of the problem you are experiencing
  • A mockup of the results you want to achieve
  • Your current work-in-progress PBIX file
  • Your underlying data file (to allow us to go into Power Query if necessary to transform your data and/or data model – often DAX questions really end up being data modeling solutions)

Check out this thread on Tools and Techniques for Providing PBIX Files with Your Forum Questions

Not completing your data may sometimes cause delay in getting an answer.

3

Hi Guys,

Please find below info as requested:

Mockup of the results you want to achieve:

image
image339×565 15.8 KB

Your Current work-in-progress PBIX file:
New-Dropped Accounts.pbix (512.2 KB)

Hope this helps.

4

Thanks for sending in the additional info. Bumping this post for more visibility.

5

Hi @Lazola

Can you try below measure and check if you get desired results. it is primarily checking for Trans_Id with values in CD and no values in PD irrespective of Age groups.

#TxnInCurrNotInPrev = VAR dC = [#DtC]
VAR dP = [#DtP]
var txn_Id = 
            EXCEPT (
            CALCULATETABLE (
                values(Book2[TXN_ID] ),
                'Date'[PrevBusinessDay] = dC,all(Book2[Age_Bucket])
            ),
                            CALCULATETABLE (
                    values(Book2[TXN_ID] ),
                    'Date'[PrevBusinessDay] = dP,all(Book2[Age_Bucket])
            ))
              
return
COUNTROWS(FILTER(Book2,Book2[TXN_ID] in (txn_Id) && Book2[Merged] = DC))

Thanks
Ankit J

6

Thank you so much @ankit - Worked like a charm!!! Much appreciated :pray:t5: :pray:t5: :pray:t5:

2 Likes