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

[Sut_Datanaut]

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..

Name	Orders	Month	Year
Jim	1	1	2022
Jim	3	2	2022
Jim	65	3	2022
Jim	43	4	2022
Jim	5	5	2022
Jim	5	6	2022
Bob	54	1	2022
Bob	44	2	2022
Bob	68	3	2022
Bob	5	4	2022
Bob	4	5	2022
Bob	3	6	2022

Name	Orders	Month	Year	MonthYear	Generic Monthly Name
Jim	1	1	2022	12022	Month05
Jim	3	2	2022	22022	Month04
Jim	65	3	2022	32022	Month03
Jim	43	4	2022	42022	Month02
Jim	5	5	2022	52022	Month01
Jim	5	6	2022	62022	Month00
Bob	54	1	2022	12022	Month05
Bob	44	2	2022	22022	Month04
Bob	68	3	2022	32022	Month03
Bob	5	4	2022	42022	Month02
Bob	4	5	2022	52022	Month01
Bob	3	6	2022	62022	Month00

[MFelix]

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