Date Range calculation using Dax

binayjethwa — Fri, 21 Jun 2024 09:28:03 GMT

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

Re: Date Range calculation using Dax

mark_endicott — Fri, 21 Jun 2024 11:50:02 GMT

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.

topic Re: Date Range calculation using Dax in DAX Commands and Tips

Date Range calculation using Dax

Re: Date Range calculation using Dax