Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 @v-yiruan-msft @tamerj1 

Super User
Super User

Frequent Visitor

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

Thank you

Super User
Super User

@Dataholic , Seems like this one to me


Customer Retention Part 3: Period Of Stay – Cohort Analysis:

Helpful resources


Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.