Power BI 4,4,4 Calendar and 4,4,5 Calendar
Objective:
Create 4,4,4 Calendar and 4,4,5 Calendar using DAX
DAX Code 4,4,4 Calendar
Cal 444 = var _cal = CALENDAR(Date(2018,01,01), Date(2022,12,31))
return ADDCOLUMNS( _cal
,"Year" , QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Year" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Month" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Year Month" , (QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1)*100 + Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Week" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1
)
DAX Code 4,4,5 Calendar
Cal 445 = var _cal = CALENDAR(Date(2018,01,01), Date(2022,12,31))
return ADDCOLUMNS( _cal
,"Year" , QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Day Of Year" , Mod( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1
,"Qtr" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4) +1
,"Week of Month" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13)
var _2 = if(_1=12, 5, mod(_1,4) +1)
return _2
,"Month no" , var _1 = mod(Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52),13)
var _2 = if(_1=12, 3, QUOTIENT( _1,4) +1)
return Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,91),4)*3 + _2
,"Year Month" , (QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,364)+1)*100 + Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,28),13)+1
,"Week" , Mod(QUOTIENT( DATEDIFF(Minx(_cal, [Date]), [Date], day) ,7),52)+1
)
You download the attached file.
