Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have to calculate Day no of month and Day no of quarter using dax help me please.
Solved! Go to Solution.
@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
@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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |