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
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
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
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

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

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors