cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dataholic
Frequent Visitor

Label month serially from selected periods in DAX

Hello all,


I am working with a data set that contains the month in which the customers subscribed for services, and their monthly payment subsequently.

The goal is to try and determine the number of customers that dropped off or who didn't make payments on a subsequent basis. I have been able to determine the count of customers on a monthly basis who dropped off.

 

The data looks like this. I have put it in excel to simplify things 

 

  Monthly Payments
Month subscribed Jan-21Feb-21Mar-21Apr-21May-21Jun-21Jul-21Aug-21Sep-21Oct-21Nov-21Dec-21Jan-22Feb-22Mar-22Apr-22
Jan-21 900867834801768735702669636603570537504471438 
Feb-21  800767734701668635602569536503470437404371 
Mar-21   940907874841808775742709676643610577544 
Apr-21    939906873840807774741708675642609576 
May-21     3102772442111781451127946130 
Jun-21      893860827794761728695662629596 
Jul-21       493460427394361328295262229 
Aug-21        920887854821788755722689 
Sep-21         120875421000 
Oct-21          2221891561239057 
Nov-21           294261228195162 
Dec-21            748715682649 

 

What I am trying to get at is to normalize the monthly progression of monthly payments relative to the subscription month to look like this;

 

  Monthly Payments
Month subscribed Month 1Month 2Month 3Month 4Month 5Month 6Month 7Month 8Month 9Month 10Month 11Month 12Month 13Month 14Month 15Month 16
Jan-21 900867834801768735702669636603570537504471438 
Feb-21 800767734701668635602569536503470437404371  
Mar-21 940907874841808775742709676643610577544   
Apr-21 939906873840807774741708675642609576    
May-21 3102772442111781451127946130     
Jun-21 893860827794761728695662629596      
Jul-21 493460427394361328295262229       
Aug-21 920887854821788755722689        
Sep-21 120875421000         
Oct-21 2221891561239057          
Nov-21 294261228195162           
Dec-21 748715682649            

 

Please bear in mind that to normalize this, I have to write a DAX to recognize the subscription month as "Month 1" and the subsequent months as "Month 2, Month 3, ..."

Please help with this @amitchandak @yingyinr @tamerj1 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Dataholic , Seems like this one to me

 

Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

View solution in original post

2 REPLIES 2
Dataholic
Frequent Visitor

I was actually on my way to solving the months labelling. This solves it perfectly.

Thank you

amitchandak
Super User
Super User

@Dataholic , Seems like this one to me

 

Customer Retention Part 3: Period Of Stay – Cohort Analysis: https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-3-Period-Of-Stay-Cohort-Anal...



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Show episode 9

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors