Forum Discussion

Justas4478's avatar
Justas4478
Post Prodigy
1 year ago
Solved

Calendar 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 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.


Need Help
  • Kedar_Pande's avatar
    Kedar_Pande
    1 year ago
    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

7 Replies

  • SamWiseOwl's avatar
    SamWiseOwl
    Super User
    1 year ago

    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)

    )

  • Kedar_Pande's avatar
    Kedar_Pande
    Super User
    1 year ago

    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

      • Kedar_Pande's avatar
        Kedar_Pande
        Super User
        1 year ago

        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

  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    Super User
    1 year ago

    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()
    )