Forum Discussion
Custom quarter
Hello,
I need to create a custom quarter column because my company uses a particular quarter dates.
The quarteers are defined as:
Q1 From 16/01 to 15/04
Q2 From 16/04 to 15/07
Q3 from 16/07 to 15/10
Q4 from 16/10 to 15/01
How can convert dates in Q1,Q2,Q3 and Q4 using new column through DAX?
Many Thanks
You could add a new column to your date table like
Company Quarter = SWITCH( TRUE(), [Date] >= DATE( [Year], 1, 16) && [Date] <= DATE([Year],4, 15), "Q1", [Date] >= DATE( [Year], 4, 16) && [Date] <= DATE([Year],7, 15), "Q2", [Date] >= DATE( [Year], 7, 16) && [Date] <= DATE([Year],10, 15), "Q3", "Q4" )
2 Replies
Anonymous , Create a calendar like
Date = Var _1 = Addcolumns( CALENDAR(date(2020,01,01) , date(2022,12,31))
, "Start Year", if(format([date], "MMDD")*1 <= 0115 , date(year([Date])-1, 1,16) , date(year([Date]), 1,16))
, "End Year", if(format([date], "MMDD")*1 <= 0115 , date(year([Date]), 1,15) , date(year([Date])+1, 1,15))
,"Start Month", if(day([Date]) <=15, EOMONTH([Date],-2)+16, EOMONTH([Date],-1)+16)
,"End Month", if(day([Date]) <=15, EOMONTH([Date],-1)+15, EOMONTH([Date],0)+15)
, "Qtr No", Quotient(datediff(if(format([date], "MMDD")*1 <= 0115 , date(year([Date])-1, 1,16) , date(year([Date]), 1,16)), if(day([Date]) <=15, EOMONTH([Date],-2)+16, EOMONTH([Date],-1)+16), month),3)+1
, "MMDD",format([date], "MMDD")*1)
var _2 = ADDCOLUMNS(_1, "Qtr Start Date", minx(filter(_1, [Qtr No] =EARLIER([Qtr No]) ),[Start Month]))
return _2DAX Calendar - Standard Calendar, Non-Standard Calendar, 4-4-4 Calendar
https://www.youtube.com/watch?v=IsfCMzjKTQ0&t=145sYou could add a new column to your date table like
Company Quarter = SWITCH( TRUE(), [Date] >= DATE( [Year], 1, 16) && [Date] <= DATE([Year],4, 15), "Q1", [Date] >= DATE( [Year], 4, 16) && [Date] <= DATE([Year],7, 15), "Q2", [Date] >= DATE( [Year], 7, 16) && [Date] <= DATE([Year],10, 15), "Q3", "Q4" )
