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

Turn MonthYear field into generic named fields based on the most current month (Month00, Month01)

I have a quite unique situation that requires a bit of non-traditional methods. In my base data, I have two fields that show Month and Year, which duplicates name fields because of the monthly splits. I needed to transpose the month and year fields to be able to do calcualtions like monthly difference, growth rates and groupings,

 

I fixed this by creating columns for the MonthYear combination, then transforming through pivoting the column on import. This does work, but the unique names that the month year produces makes it difficult to adjust queries when a new month populates. 

 

Is there a caluclated column formula that can turn the latest month year into Month00 and the previous month Month01 etc..

 

NameOrdersMonthYear
Jim112022
Jim322022
Jim6532022
Jim4342022
Jim552022
Jim562022
Bob5412022
Bob4422022
Bob6832022
Bob542022
Bob452022
Bob362022

 

NameOrdersMonthYearMonthYearGeneric Monthly Name
Jim11202212022Month05
Jim32202222022Month04
Jim653202232022Month03
Jim434202242022Month02
Jim55202252022Month01
Jim56202262022Month00
Bob541202212022Month05
Bob442202222022Month04
Bob683202232022Month03
Bob54202242022Month02
Bob45202252022Month01
Bob36202262022Month00
1 REPLY 1
MFelix
Super User
Super User

Hi  @Sut_Datanaut ,

 

Don't understand why you are pivoting the columns and then making the transpose of the values, in this case believe that the best option since you have the month and year why don't you simply create a date column with the end of the month for each year month combination?

 

In this case you would not need to unpivot no values and you would have a date field to create the monthly differences and so on, you can even then create a column for the absolut month based on that date.

 

In the query editor just add the following code:

Date.EndOfMonth ( #date([Year],[Month],1))

 

For the relative month add the following code:

Number.Round(Number.From(( Date.From (DateTime.LocalNow()) - [End Month Date])/( 365.25 / 12 )) , 0 )+ 1

MFelix_0-1660405283109.png

 

 


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
collabdays lisbon

CollabDays Lisbon - 26 November 2022

Sessions include practical, hands-on experience that will help you take the next step in your career and know-how.

Difinity Conference

Difinity Conference 2022

Difinity is the largest Microsoft Data, AI, Power BI, Power Platform and Business Applications Conference in New Zealand.

European Share Point Conference

European Share Point Conference

This conference returns live and in-person in Copenhagen this 28 November-01 December with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.