Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |