Forum Discussion
DAX Time Patterns
I have to calculate Day no of month and Day no of quarter using dax help me please.
mshakir , You have startofmonth and startofquarter
take date diff from this you will get month day and quarter day
STQ = startofquarter(Date[Date])
Day of Qtr = datediff(STQ ,Date[Date],DAY)
HI mshakir,
You can direct create a new calculated column to use the 'day' function to extract the current day no of month. For 'day of quarter', you can refer to following calculated column formula:
DayofMonth=DAY('Calendar'[Date])DayofQuarter = VAR cQuarter = INT ( MONTH ( 'Calendar'[Date] ) / 3 ) + IF ( MONTH ( 'Calendar'[Date] ) < 3 && MOD ( MONTH ( 'Calendar'[Date] ), 3 ) > 0, 1, 0 ) RETURN COUNTROWS ( CALENDAR ( DATE ( YEAR ( 'Calendar'[Date] ), IF ( cQuarter > 1, ( cQuarter - 1 ) * 3, cQuarter ), 1 ), 'Calendar'[Date] ) )Regards,
Xiaoxin Sheng
4 Replies
Hi mshakir
Quarter No = QUARTER(Table[Date])Day No = DAY(Table[Date])This is the date table and my perspective is to calculate columns (DayNoOfQuarter and DayNoOfMonth) same like DayNoOfYear as given in screenshot. i need Day Number for both Quarter and Month neither the Quarter Number nor the Month Number
HI mshakir,
You can direct create a new calculated column to use the 'day' function to extract the current day no of month. For 'day of quarter', you can refer to following calculated column formula:
DayofMonth=DAY('Calendar'[Date])DayofQuarter = VAR cQuarter = INT ( MONTH ( 'Calendar'[Date] ) / 3 ) + IF ( MONTH ( 'Calendar'[Date] ) < 3 && MOD ( MONTH ( 'Calendar'[Date] ), 3 ) > 0, 1, 0 ) RETURN COUNTROWS ( CALENDAR ( DATE ( YEAR ( 'Calendar'[Date] ), IF ( cQuarter > 1, ( cQuarter - 1 ) * 3, cQuarter ), 1 ), 'Calendar'[Date] ) )Regards,
Xiaoxin Sheng
mshakir , You have startofmonth and startofquarter
take date diff from this you will get month day and quarter day
STQ = startofquarter(Date[Date])
Day of Qtr = datediff(STQ ,Date[Date],DAY)
