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

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors