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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

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
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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