Calendar date column for specific start - end date

[Justas4478]

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 that only starts from 01/06 of this year (it would be 2024) and end on 31/05 next year in this case it would be 2025.
I havent done this before and I dont know if it is possible.
I hope that there is some solution that would avoid the need to specify specific year in the solution.

[Bibiano_Geraldo]

Hi,
Create a new column in your calendar table and past the following DAX.
Note: Make sure to change column and table name to your owns.
If this answer satisfy your question, please mark it as soluction.

Column =

VAR CurrentYear = YEAR(TODAY())

VAR vStart = DATE(CurrentYear , 6 , 1)

VAR vEnd = DATE(CurrentYear + 1 , 5 , 31)

RETURN

IF(

    'Calendar'[Date] >= vStart && 'Calendar'[Date] <= vEnd,

    'Calendar'[Date] ,

    BLANK()

)

[Kedar_Pande]

@Justas4478 

You 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

[Justas4478]

@Kedar_Pande Hi your solution works, but is it possible to do this in power query?

[Kedar_Pande]

@Justas4478 

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 · Accepted solution]

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]

Hi @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)

)