Forum Discussion
Post ProdigyCalendar date column for specific start - end date
let
// Your existing steps here
PreviousStep = ... , // Placeholder for your existing step
// Get current year
CurrentYear = Date.Year(Date.From(DateTime.LocalNow())),
// Define the start and end dates for the custom date range
StartDate = #date(CurrentYear, 6, 1), // 1st June of the current year
EndDate = #date(CurrentYear + 1, 5, 31), // 31st May of the next year
// Add custom column for the date range check
CustomColumn = Table.AddColumn(
PreviousStep,
"CustomDateRange",
each if [Date] >= StartDate and [Date] <= EndDate then [Date] else null
)
in
CustomColumn
Super UserYou can create a custom column in your calendar table
CustomDateRange =
VAR StartDate = DATE(YEAR(TODAY()), 6, 1)
VAR EndDate = DATE(YEAR(TODAY()) + 1, 5, 31)
RETURN
IF(
'Calendar'[Date] >= StartDate && 'Calendar'[Date] <= EndDate,
'Calendar'[Date],
BLANK()
)
This logic dynamically sets the range from June 1st of the current year to May 31st of the next year. The column will only return dates within that range and leave others as blank.
💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn
Post ProdigyKedar_Pande Hi your solution works, but is it possible to do this in power query?
- Kedar_Pande
let
CurrentYear = Date.Year(Date.From(DateTime.LocalNow())),
StartDate = #date(CurrentYear, 6, 1), // 1st June of the current year
EndDate = #date(CurrentYear + 1, 5, 31), // 31st May of the next year
CustomColumn = Table.AddColumn(
PreviousStep,
"CustomDateRange",
each if [Date] >= StartDate and [Date] <= EndDate then [Date] else null
)
in
CustomColumn- Justas4478
Kedar_Pande I am getting this error when I try to add custom column in power query.
This is majory of query for calendar.
- Kedar_Pande
let
// Your existing steps here
PreviousStep = ... , // Placeholder for your existing step
// Get current year
CurrentYear = Date.Year(Date.From(DateTime.LocalNow())),
// Define the start and end dates for the custom date range
StartDate = #date(CurrentYear, 6, 1), // 1st June of the current year
EndDate = #date(CurrentYear + 1, 5, 31), // 31st May of the next year
// Add custom column for the date range check
CustomColumn = Table.AddColumn(
PreviousStep,
"CustomDateRange",
each if [Date] >= StartDate and [Date] <= EndDate then [Date] else null
)
in
CustomColumn