Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
@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.
@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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
8 | |
6 |