Date Range calculation using Dax

[binayjethwa]

Hi , 

I have a requirement to calculate date range using dax.

I need to create a date rage column for below dates.

SeasonDatesDenominator

Non Busy	1 Jul 2024 - 13 Oct 2024	45
Busy	14 Oct 2024 - 30 Nov 2024	50
Non Busy	2 Dec 2024 - 12 Jan 2025	45
Busy	13 Jan 2025 – 4 May 2025	55
Non Busy	5 May 2025 – 27 Jun 2025	45

I am using below measure to create a colum so if i select that date range , all the dates in that range will come using slicer.

If i use below formula , i am not getting the date range for 2 Dec - 12 Jan as i see blank values.

Month_Range_India =

VAR MonthDay = ('Raw Dump'[Day])

VAR MonthNum = ('Raw Dump'[Month])

var year = YEAR('Raw Dump'[Date])

return

    SWITCH(TRUE(),

    'Raw Dump'[Date]>=date(year,12,2) && 'Raw Dump'[Date]<=date(year,1,12),"2 Dec - 12 Jan",

     'Raw Dump'[Date]>=date(year,1,13) && 'Raw Dump'[Date]<=date(year,5,4),"13 Jan - 4 May",

      'Raw Dump'[Date]>=date(year,5,5) && 'Raw Dump'[Date]<=date(year,6,27),"5 May - 27 Jun",

    'Raw Dump'[Date]>=date(year,7,1) && 'Raw Dump'[Date]<=date(year,10,13),"1 Jul - 13 Oct",

    'Raw Dump'[Date]>=date(year,10,14) && 'Raw Dump'[Date]<=date(year,11,30),"14 Oct - 30 Nov",

 BLANK())

And if i use below formula , i am getting blanks for 6 Jan 2025 week which should be included in that date range.

Month_Range_India =

VAR MonthDay = ('Raw Dump'[Day])

VAR MonthNum = ('Raw Dump'[Month])

var year = YEAR('Raw Dump'[Date])

return

    SWITCH(TRUE(),

    'Raw Dump'[Date]>=date(year,12,2) && 'Raw Dump'[Date]<=date(year+1,1,12),"2 Dec - 12 Jan",

     'Raw Dump'[Date]>=date(year,1,13) && 'Raw Dump'[Date]<=date(year,5,4),"13 Jan - 4 May",

      'Raw Dump'[Date]>=date(year,5,5) && 'Raw Dump'[Date]<=date(year,6,27),"5 May - 27 Jun",

    'Raw Dump'[Date]>=date(year,7,1) && 'Raw Dump'[Date]<=date(year,10,13),"1 Jul - 13 Oct",

    'Raw Dump'[Date]>=date(year,10,14) && 'Raw Dump'[Date]<=date(year,11,30),"14 Oct - 30 Nov",

 BLANK())

 Is there any other way i can achieve this without blanks and get all date ranges.

Thanks in advanve.

Thanks,

Binay 

[mark_endicott · Accepted solution]

@binayjethwa - You can use this formula, just change the table name:

var _Year = YEAR('Table'[Date 1])
return
    SWITCH(TRUE(),
    'Table'[Date 1]>=date(_year,12,2) && 'Table'[Date 1]<=date(_year+1,1,12),"2 Dec - 12 Jan",
    'Table'[Date 1]>=date(_year,1,3) && 'Table'[Date 1]<=date(_year,5,4),"13 Jan - 4 May",
    'Table'[Date 1]>=date(_year,5,5) && 'Table'[Date 1]<=date(_year,6,27),"5 May - 27 Jun",
    'Table'[Date 1]>=date(_year,7,1) && 'Table'[Date 1]<=date(_year,10,13),"1 Jul - 13 Oct",
    'Table'[Date 1]>=date(_year,10,14) && 'Table'[Date 1]<=date(_year,11,30),"14 Oct - 30 Nov",

 BLANK())

Screenshot shows it works for me, if it works for you please mark it as the solution.