Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi DAX Masters,
I have a requirement to find the Start and End Date of the current quater,
for instance
for this quater it should be StartDay = 1/7/2021 and EndDate = 30/9/2021
this needs to be calculated dynamically based on the current date as measures.
Thanks a lot in advance
Regards
Amal Eranda
Solved! Go to Solution.
Simple enough,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I was assuming the OP did not have a date table. Do you know of a way to do it without one, using just TODAY()?
Edit: @amalrio if you do have a dates table then @CNENFRNL measures are the way to go.
Start of QTR = CALCULATE ( STARTOFQUARTER ( Dates[Date] ), Dates[Date] = TODAY() )
End of QTR = CALCULATE ( ENDOFQUARTER ( Dates[Date] ), Dates[Date] = TODAY() )
Hello all the experts, I am really thankful for all of your sugessions. yes I do have a date and my DAX knowladge was not mature enough @CNENFRNL thanks a lot.
If I have the opprotunity I would accept both answers as the solution but in this case I will accept @jdbuchanan71 as he has both the method covered,
Thanks a lot @CNENFRNL agian.
There are out-of-the-box time intelligence functions in DAX, STARTOFQUARTER/ENDOFQUARTER.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Simple enough,
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
I was assuming the OP did not have a date table. Do you know of a way to do it without one, using just TODAY()?
Edit: @amalrio if you do have a dates table then @CNENFRNL measures are the way to go.
Start of QTR = CALCULATE ( STARTOFQUARTER ( Dates[Date] ), Dates[Date] = TODAY() )
End of QTR = CALCULATE ( ENDOFQUARTER ( Dates[Date] ), Dates[Date] = TODAY() )
BTW,
SoQ = DATE( YEAR( TODAY() ), ( QUARTER( TODAY() ) - 1 ) * 3 + 1, 1 )
EoQ = DATE( YEAR( TODAY() ), QUARTER( TODAY() ) * 3 + 1, 1 ) - 1
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Ah nice, using QUARTER, I like it.
By chance, I found some UNINTUITIVE arguments like DATE(2021,-1,33) and DATE(2021,13,-1) are valid; share with you guys, cheers!
Enjoy DAX!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
The start of the quarter for the current date can be calculated like this.
Start of quarter =
VAR _Today = TODAY ()
RETURN
DATE ( YEAR ( _Today ), ROUNDUP ( DIVIDE ( MONTH ( _Today ), 3 ), 0 ) * 3 - 2, 1 )
Then then end of the quarter is like done like this.
End of quarter = EOMONTH ( [Start of quarter], 2 )
User | Count |
---|---|
84 | |
69 | |
69 | |
65 | |
54 |
User | Count |
---|---|
93 | |
93 | |
91 | |
76 | |
69 |