cancel
Showing results 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

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 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,

Binay

1 ACCEPTED SOLUTION
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())``````

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

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

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors