thiscodeWorks - Organizing the best of code online

#ms.pbi #dax #calendar.table #dax.switch #dax.true #dax.month #quarter

Qtr =

SWITCH(
	TRUE(),
	MONTH('Calendar[Date]') <=3, "Q1",
	MONTH('Calendar[Date]') <=6, "Q2",
	MONTH('Calendar[Date]') <=9, "Q3",
	"Q4"
)

#ms.pbi #dax #calendar.table #week.no

DAX | Year + WeekNo

YearWeekNo = 

'Calendar'[Year]*100 + 'Calendar'[WeekNo]

#ms.pbi #dax #calendar.table #dax.day

DAX | Day of Month No

DayofMonthNo =

DAY ( 'Calendar'[Date] )

#ms.pbi #dax #dax.weekday #calendar.table

DAX | Day of Week No

DayofWeekNo = 
WEEKDAY ( [Date], 2 )

#ms.pbi #dax #calendar.table #dax.format #dax.calendar #format-ddd

DAX | Day Name

DayName = 

FORMAT ( 'Calendar'[Date], "ddd" )

#ms.pbi #dax #calendar.table #dax.weeknum

DAX | Week No - 1

WeekNo = 

"W" & WEEKNUM( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.rankx #dax.asc #dax.dense #ranking #week.rank

DAX | Week Ranking

Week Rank = 

RANKX(
    ALL( 'Calendar' ),
    'Calendar'[Week StartDate], , ASC, Dense)

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week Start Date - 1 (Week Begins on Monday)

Week StartDate = 

'Calendar'[Date] + -1*WEEKDAY( 'Calendar'[Date], 2 ) + 1

#ms.pbi #dax #calendar.table #dax.weekday

DAX | Week End Date

Week EndDate = 

'Calendar'[Date] + 7-1*WEEKDAY( 'Calendar'[Date], 2 )

#ms.pbi #dax #calendar.table #dax.quarter

DAX | Quarter No - 1

QtrNo = 

"Q" & QUARTER( 'Calendar'[Date] )

#ms.pbi #dax #calendar.table #dax.addcolumns #dax.var #dax.return #dax.format #dax.month

DAX | Creating Calendar Table Using CALENDARAUTO

Calendar = 
  
VAR CalTable = CALENDARAUTO ()
VAR MyCal =
    ADDCOLUMNS (
        CalTable,
        "Index", MONTH ( [Date] ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Qtr", FORMAT ( [Date], "\QQ" ),
        "Year", FORMAT ( [Date], "yyyy" )
    )
RETURN
    MyCal

DAX | Quarter No - 2

DAX | Year + WeekNo

DAX | Day of Month No

DAX | Day of Week No

DAX | Day Name

DAX | Week No - 1

DAX | Week Ranking

DAX | Week Start Date - 1 (Week Begins on Monday)

DAX | Week End Date

DAX | Quarter No - 1

DAX | Creating Calendar Table Using CALENDARAUTO

Save snippets that work with our extensions