Showing results for 
Search instead for 
Did you mean: 
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

Helpful resources

PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Top Solution Authors
Top Kudoed Authors