Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
binayjethwa
Helper IV
Helper IV

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 
 
1 ACCEPTED SOLUTION
mark_endicott
Super User
Super User

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

 

mark_endicott_0-1718970556410.png

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

View solution in original post

1 REPLY 1
mark_endicott
Super User
Super User

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

 

mark_endicott_0-1718970556410.png

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

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.