Forum Discussion

mshakir's avatar
mshakir
Regular Visitor
6 years ago
Solved

DAX Time Patterns

I have to calculate Day no of month and Day no of quarter using dax help me please.
Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    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)

  • Anonymous's avatar
    Anonymous
    6 years ago

    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's avatar
mshakir
Regular Visitor
6 years ago

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

 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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