cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dpotta
Resolver I
Resolver I

Help with date diff type formula

Hi

 

I am trying to write a formula in a DimDate Table, please can soomeone help with the rest of the formula!

 

The values in the table are:

 

DateMonth NameMonth Number 
01/01/2021Jan1 
01/02/2021Feb2 
01/03/2021Mar3 
01/04/2021Apr4 
01/05/2021May5 
01/06/2021Jun6 
01/07/2021Jul7 
01/08/2021Aug8 
01/09/2021Sep9 
01/10/2021Oct10 
01/11/2021Nov11 
01/12/2021Dec12 
01/01/2021Jan1 
01/02/2021Feb2 
01/03/2021Mar3 
01/04/2021Apr4 
01/05/2021May5 
01/06/2021Jun6 
01/07/2021Jul7 
01/08/2021Aug8 
01/09/2021Sep9 
01/10/2021Oct10 
01/11/2021Nov11 
01/12/2021Dec12 


I am then wanting a new column called  [Relative Month Number] where Month 1 is the Month of todays date, Month 2 is the the number of 1st of the month of the preceding date going back based on 12 months. 

 

And, in dates for preceding years the 13th month back would also be 1, 14th month back 2 etc.

 

So the table would look like this based on todays date of the 22/02/2021

 

DateMonth NameMonth NumberRel Month Number
01/01/2021Jan112
01/02/2021Feb21
01/03/2021Mar32
01/04/2021Apr43
01/05/2021May54
01/06/2021Jun65
01/07/2021Jul76
01/08/2021Aug87
01/09/2021Sep98
01/10/2021Oct109
01/11/2021Nov1110
01/12/2021Dec1211
01/01/2021Jan112
01/02/2021Feb21
01/03/2021Mar32
01/04/2021Apr43
01/05/2021May54
01/06/2021Jun65
01/07/2021Jul76
01/08/2021Aug87
01/09/2021Sep98
01/10/2021Oct109
01/11/2021Nov1110
01/12/2021Dec1211


I start off writing a variable varToday = 

 


today = Date.From(DateTime.LocalNow()),

 

And then my formula for the Rel Month Number Column begins

 

Rel Month Number

if

Date.Month([Date]) = Date.Month(varToday)

then 1

else

and then I am struggling with the rest.

 

Is this the start of a good solution, or is there a better way to look at this?

1 ACCEPTED SOLUTION
Rocco_sprmnt21
Super User II
Super User II

without any if then els, letting the date.add month function do the dirty work

 

 

Table.AddColumn(#"Aggiunta colonna personalizzata", "relM1", each Date.Month(Date.AddMonths([Date],13 -Date.Month(DateTime.LocalNow()))))

 

View solution in original post

3 REPLIES 3
Rocco_sprmnt21
Super User II
Super User II

without any if then els, letting the date.add month function do the dirty work

 

 

Table.AddColumn(#"Aggiunta colonna personalizzata", "relM1", each Date.Month(Date.AddMonths([Date],13 -Date.Month(DateTime.LocalNow()))))

 

View solution in original post

Many thanks @Rocco_sprmnt21 : )

Rocco_sprmnt21
Super User II
Super User II

 

 

Table.AddColumn(#"Modificato tipo", "relM", each let rmn=-Date.Month(DateTime.LocalNow())+[Month Number]+1 in if rmn>0 then rmn else rmn+12)

 

 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors