Forum Discussion
Justas4478
Post Prodigy
Post ProdigyCalendar date column for specific start - end date
Hi, I have calendar table and date column that is between 2023- 2025 and it automaticly changes based on earliest and latest dates in the data. I want to create another column in my calendar table t...
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
SamWiseOwl
Super User
Super UserHi Justas4478
Calendar([Date1], [Date2]) creates a list between X dates.
If you want to add a column it sounds similar to calculating the financial year.
There is a full blog here: https://www.wiseowl.co.uk/blog/s2947/calendarauto-table.htm
Financial Year = IF(
[Date] >= DATE(Year([Date]), 6, 1),
Year([Date]) & "/" & RIGHT(Year([Date]) +1,2), //Change these to what you need
Year([Date])-1 &"/" & RIGHT(Year([Date]),2)
)