Forum Discussion
Help understanding how a DAX date formula works
Hi there,
I'm new to DAX and am looking for a way to say whether a given date is in the current quarter or the previous 4 quarters. I came across a formula that one of our former consultants developed and modified it a bit to get the results I am looking for, but I'm having trouble deciphering how it works and if it will continue to give me the results I'm looking for in the future. Can someone help me understand all the moving parts in this formula:?
=IF(
CONCATENATE(YEAR(TODAY()),ROUNDUP(MONTH(TODAY())/3,0)) - CONCATENATE('Date'[Year],'Date'[CalendarQuarterNumber]) <=10
&& CONCATENATE(YEAR(TODAY()),ROUNDUP(MONTH(TODAY())/3,0)) - CONCATENATE('Date'[Year],'Date'[CalendarQuarterNumber]) >=0,
"Yes",
"No"
)
Thank you!
In this formula, "ROUNDUP(MONTH(TODAY())/3
,0))" is the Quarter Number of TODAY(), then you concatenate with Year() number. It will be like "20164","20171", "20172","20173","20174", When you have that "Year-Quarter" minus the Year+QuartNumber in your table row, if the number if less than 10 and greater than 0, it means the date is within previous 4 quarters of TODAY(). For exmaple, today is "20172", you can get dates between "20162" and "20172".
Regards,
Regards,
1 Reply
v-sihou-msftMicrosoft Employee
In this formula, "ROUNDUP(MONTH(TODAY())/3
,0))" is the Quarter Number of TODAY(), then you concatenate with Year() number. It will be like "20164","20171", "20172","20173","20174", When you have that "Year-Quarter" minus the Year+QuartNumber in your table row, if the number if less than 10 and greater than 0, it means the date is within previous 4 quarters of TODAY(). For exmaple, today is "20172", you can get dates between "20162" and "20172".
Regards,
Regards,