Forum Discussion

binayjethwa's avatar
binayjethwa
Helper V
2 years ago
Solved

Date Range calculation using Dax

Hi , 

 

I have a requirement to calculate date range using dax.

 

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

 

SeasonDatesDenominator

Non Busy1 Jul 2024 - 13 Oct 202445
Busy14 Oct 2024 - 30 Nov 202450
Non Busy2 Dec 2024 - 12 Jan 202545
Busy13 Jan 2025 – 4 May 202555
Non Busy5 May 2025 – 27 Jun 202545

 

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 
 
Need Help
  • mark_endicott's avatar
    mark_endicott
    2 years ago

    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. 

1 Reply

  • mark_endicott's avatar
    mark_endicott
    Super User
    2 years ago

    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.