cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
NickA01
Resolver II
Resolver II

Month on Month and Year on year in Power Query

Hi All,  
I got sent a pbix file on Friday where someone had built a single column table to provide today's date and then adding  one month to this for the next n months.  This was done by creating new calculated column for each additional month and adding on 1 month to previous. Once they had all the columns they wanted, the columns were then pivoted to get the desired single column table. : 
They had done the same for each year.
I personally thought this was a bit inefficient and after spending some time on google and rewriting some code I found here (LINK) , I came up with this: 

I hope this is of use to someone and will save other community members spending time searching. 


//Code for MoM //

let
// in range, enter start value .. end value : Note is you want previous mths then -x..end value//
Source = Table.FromList({0..12}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "Today", each Date.AddMonths(Date.From(DateTime.LocalNow()),[Column1]), type date),
RemovedColumn = Table.RemoveColumns(AddedStartOfMonth,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(RemovedColumn,{{"Today", "Dates in the next 6 months"}})
in
#"Renamed Columns"

// Code to YoY from today's date //
let
Source = Table.FromList({0..4}, each{_}),
AddedStartOfMonth = Table.AddColumn(Source, "Today", each Date.AddYears(Date.From(DateTime.LocalNow()),[Column1]), type date),
RemovedColumn = Table.RemoveColumns(AddedStartOfMonth,{"Column1"}),
#"Renamed Columns" = Table.RenameColumns(RemovedColumn,{{"Today", "Dates in the next 6 months"}})
in
#"Renamed Columns"



1 REPLY 1
v-yingjl
Community Support
Community Support

Hi @NickA01 ,

Thanks for your sharing!

Believe it could help more members in the community to create the MoM or YoY date column easily😀.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors