Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Date Table that returns dates of the current month and next 2 months and Week of the Year

Hello,

 

I would like to create a date table in Power Query that returns the dates of the current month and the next 2 months in addition to the Week of the Year for the current month and the next 2 months, may I know how I can go about doing this please?

 

Thanks for the help!

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Try this power Query Code

 

let
StartDate = Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())) ,
EndDate = Date.EndOfMonth(Date.AddMonths( Date.From(DateTime.FixedLocalNow()),2)),
Source = List.Dates( StartDate,
Duration.Days( EndDate- StartDate) +1,
#duration(1,0,0,0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StartOfWeek", each Date.StartOfWeek([Date],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartOfYear", each Date.StartOfYear([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date( DateTime.FixedLocalNow())),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom", each if Date.IsInCurrentMonth([Date]) then "This Month"
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "Next N month", each Date.IsInNextNMonths([Date],2)),
#"Added Custom16" = Table.AddColumn(#"Added Custom15", "FY ", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
#"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "Day of Year", each Duration.Days([Date] -[#"FY "])+1),
#"Added Custom19" = Table.AddColumn(#"Added Custom18", "Month Diff", each Number.FromText( Date.ToText( Date.AddMonths([StartOfMonth],-3),"yyyyMM") )
- Number.FromText( Date.ToText( Date.AddMonths([#"FY "],-3),"yyyyMM"))+1),
#"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fy Month", each if Date.Month([Date])>=4 then
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12),
#"Added Custom21" = Table.AddColumn(#"Added Custom20", "WeekofYear", each Date.WeekOfYear([Date])),
#"Added Custom22" = Table.AddColumn(#"Added Custom21", "YearWeek", each Date.Year([Date]) *100 +Date.WeekOfYear([Date]))
in
#"Added Custom22"

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@Anonymous , Try this power Query Code

 

let
StartDate = Date.StartOfMonth(Date.From(DateTime.FixedLocalNow())) ,
EndDate = Date.EndOfMonth(Date.AddMonths( Date.From(DateTime.FixedLocalNow()),2)),
Source = List.Dates( StartDate,
Duration.Days( EndDate- StartDate) +1,
#duration(1,0,0,0) ),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "StartOfMonth", each Date.StartOfMonth([Date])),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "StartOfWeek", each Date.StartOfWeek([Date],2)),
#"Added Custom2" = Table.AddColumn(#"Added Custom1", "StartOfYear", each Date.StartOfYear([Date])),
#"Added Custom3" = Table.AddColumn(#"Added Custom2", "EndOfYear", each Date.EndOfYear([Date])),
#"Added Custom4" = Table.AddColumn(#"Added Custom3", "Day of Week (Tue)", each Date.DayOfWeek([Date],2)+1),
#"Added Custom5" = Table.AddColumn(#"Added Custom4", "DayOfWeekName", each Date.DayOfWeekName([Date])),
#"Added Custom6" = Table.AddColumn(#"Added Custom5", "DayOfYear", each Date.DayOfYear([Date])),
#"Added Custom7" = Table.AddColumn(#"Added Custom6", "Add days", each Date.AddDays([Date],-3)),
#"Added Custom8" = Table.AddColumn(#"Added Custom7", "Add Months", each Date.AddMonths([Date],2)),
#"Added Custom9" = Table.AddColumn(#"Added Custom8", "Current Time", each DateTime.LocalNow()),
#"Added Custom10" = Table.AddColumn(#"Added Custom9", "Fixed Local Now", each DateTimeZone.FixedLocalNow()),
#"Added Custom11" = Table.AddColumn(#"Added Custom10", "Todays date", each DateTime.Date( DateTime.FixedLocalNow())),
#"Added Custom12" = Table.AddColumn(#"Added Custom11", "Is In Current Day", each if Date.IsInCurrentDay([Date]) then "Today" else Date.ToText([Date])),
#"Added Custom13" = Table.AddColumn(#"Added Custom12", "Is Current Month", each Date.IsInCurrentMonth([Date])),
#"Added Custom14" = Table.AddColumn(#"Added Custom13", "Custom", each if Date.IsInCurrentMonth([Date]) then "This Month"
else if Date.IsInNextMonth([Date]) then "Next Month"
else if Date.IsInPreviousMonth([Date]) then "Last Month" else Date.ToText([Date], "MMM-yyyy")),
#"Added Custom15" = Table.AddColumn(#"Added Custom14", "Next N month", each Date.IsInNextNMonths([Date],2)),
#"Added Custom16" = Table.AddColumn(#"Added Custom15", "FY ", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) -1,4,1) else #date(Date.Year([Date]) ,4,1)),
#"Added Custom17" = Table.AddColumn(#"Added Custom16", "Fy End", each if Date.Month([Date]) < 4 then #date(Date.Year([Date]) ,3,31) else #date(Date.Year([Date])+1 ,3,31)),
#"Added Custom18" = Table.AddColumn(#"Added Custom17", "Day of Year", each Duration.Days([Date] -[#"FY "])+1),
#"Added Custom19" = Table.AddColumn(#"Added Custom18", "Month Diff", each Number.FromText( Date.ToText( Date.AddMonths([StartOfMonth],-3),"yyyyMM") )
- Number.FromText( Date.ToText( Date.AddMonths([#"FY "],-3),"yyyyMM"))+1),
#"Added Custom20" = Table.AddColumn(#"Added Custom19", "Fy Month", each if Date.Month([Date])>=4 then
Date.Month([Date])-Date.Month([#"FY "])+1
else Date.Month([Date])-Date.Month([#"FY "])+1+12),
#"Added Custom21" = Table.AddColumn(#"Added Custom20", "WeekofYear", each Date.WeekOfYear([Date])),
#"Added Custom22" = Table.AddColumn(#"Added Custom21", "YearWeek", each Date.Year([Date]) *100 +Date.WeekOfYear([Date]))
in
#"Added Custom22"

Anonymous
Not applicable

Thanks amit! The code works well

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.