Help needed with DAX Query - Customer payment details after joining

[Sim2312]

Hi Enterprise DNA Team,

I am stuck with an issue , where I need to retrieve Customer No’s and the Payments done by them each day since they became Customers.

The table should have columns like below:
Customer Name , Payments day 1, Payments day 2 …Payments day 30 and so on.

where Payment Day 1 = Create date of customer in the system.
Payment Day 2 = Create date of customer in the system+1

I have an inactive relatonship between the Payments table and Customer table on column Customers.(as there is another active relationship).

Attaching sample pbix file for your reference.Test.pbix (30.9 KB)

Can you please let me know how I can acheive this.

Thanks,

[JarrettM]

Please clarify what exactly you want done. Looking at your file, why is your relationship inactive? I could see having it inactive if you had multiple dates. Also you are missing a Date Table. Having a Date Table is necessary when doing any type of time intelligence measures.

Thanks

[Sim2312]

Hi ojones,

I have attached the modified pbix file. Both the create date and the tran date have an active relationship with the Date table, as I need it for other measures. I have also attached the results that I am expecting in the Expected result sheet of the Customers file.

Hope this clarifes.

ThanksCustomers.xlsx (14.2 KB) Test.pbix (40.0 KB)

[JarrettM]

Having a look into this now. Sending you a video in the meantime about setting up a proper Date table just case you are unaware of how to do so. The model you sent only had one column in the Date table. Also attached is a copy of Sam’s Date table code that he uses in the video.Power BI Date Table code.txt (2.8 KB)

How To Create A Detailed Date Table In Power BI Fast

Thanks

[JarrettM]

This one is a bit tricky since not all Customer’s have the same Create Date. Would be easy to come up with the Min( Day 1) & Max ( last purchase), but the purchase in between is where it gets hairy. I put all of this data into a Matrix that has the total transactions by Date. Don’t know if this works for you or not. I’ll pass this along to others to see if there is a quick solution.

Thanks

[sam.mckay]

Ok so there’s quite a bit to this one. We will need to use a combination of a few techniques.

One of the harder techniques is using formula similar to what you would use with cohort analysis. Because to create the payment 1, 2, 3 dates, you need to create a supporting table to make it work.

Time-Based Cohort Analysis

Also this is quite similar to another project based example I have done recently where there was a need to create a table with specific days after a start date…similar to what you need here

Project Management Insights w/Power BI

There’s a couple of difference to these, but some similar techniques.

This isn’t easy though I must say.

So here’s how you would do it.

First I’m just going to create a table with numbers 1 - 60 (this can be whatever you like though)

This is all you need for your model. You should never have to join two lookup tables together.

Basically your create date now is kind of like the cohort date from the example above, it’s just more granular at a daily basis

Then from here I created some formulas to bring it together

Created Date = SELECTEDVALUE( Customers[Create Date] )

Days After Joining = 
AVERAGE( 'Days Since Joining'[Days Since Joining] )

Customers Payment = 
VAR CustomerDimension = VALUES( Customers[Customer No] )

RETURN
  CALCULATE( COUNTROWS( Payments ),
        FILTER( CustomerDimension,
            COUNTROWS(
                FILTER( Payments,
                    Payments[Tran Date] - [Created Date] = [Days After Joining] ) ) > 0 ) )

This one below is the main one

And I have it in a matrix

Have a play around with the below pbix and see if this is what you were looking for
Test (1).pbix (39.4 KB)

Thanks
Sam

[sam.mckay]

I just realised that there might be some slight adjustment you need to create for the formula to get this exactly as you need. But hopefully you can work with this technique and adjust for the last bit.

Thanks
Sam