How to Create Calendar Table using DAX in PowerBI | MI Tutorials

How to Create Calendar Table using DAX in PowerBI | MI Tutorials
Calendar =
VAR Days = CALENDAR ( DATE ( 2016, 1, 1 ), DATE ( 2018, 12, 31 ) )
RETURN ADDCOLUMNS (
Days,
"Year", YEAR ( [Date] ),
"Month Number", MONTH ( [Date] ),
"Month", FORMAT ( [Date], "mmmm" ),
"Year Month Number", YEAR ( [Date] ) * 12 + MONTH ( [Date] ) - 1,
"Year Month", FORMAT ( [Date], "mmm yy" ),
"Week Number", WEEKNUM ( [Date] ),
"Week Number and Year", "W" & WEEKNUM ( [Date] ) & " " & YEAR ( [Date] ),
"WeekYearNumber", YEAR ( [Date] ) & 100 + WEEKNUM ( [Date] ),
"Is Working Day",not WEEKDAY([Date]) in {1,7}
)
www.sqlbi.com/...

[@MrLodzinjsh]

Thanks!

[@janpalecek1548]

Nice explain and usefull

[@MITutorials]

Thank you!

[@KiranSodheja]

Thank you so much👍

[@emmanuelenumah7775]

Thanks for this. I wanna ask cos I have a situation where my company uses an abnormal calendar system;
Week starts on Saturdays, march, June, Sept and December has 5 weeks while the other months have 4 weeks. How do I create a calendar table with these parameters?

[@dhruvkapoor767]

Hi Sir how can we show variance between two dates selected in slicer

[@testprime4248]

I used this with min and max or 2 filters
Var A calculates min
Var B calculates max
Return Compare A and B
You will need to play around with what is connected to what otherwise you get double filtered. In the end is not very elegant and is a lot of work but it works.

[@Himanshu.bhardwaj8]

Please create video with good video quality

[@TashaCafasso]

Hi, my question would be, in my sales table, i dont have a date column, only a quater and year column, How can i create a column in the sales table that has a date i can then create a relationship with in the date table i have now made? Thank you!

[@MITutorials]

Why you do need a calendar table when you don’t have dates in your data ? What is it that you are looking for in the calendar table ?

[@TashaCafasso]

@@MITutorials I needed it to be able to see qtr on qtr comparison and YoY, but i have managed to do this now. Thanks for the video it was really useful

[@MITutorials]

Glad you were able to do it!

[@shreeee8973]

@@TashaCafasso Hi Tasha i am having same problem where i dont have dates in my data and want to see qtr on qtr comparision
please let me know if there's any video that helped you

[@YT4TV-gy6cs]

How can I have STARTOFYEAR function in this code?

[@raj7sam]

Good info but can you tell me what is the advantage of using the date table separately?

[@MITutorials]

You will have lot of control to create customisations especially when it comes to time intelligence functions and creating custom fiscal years etc.

[@priyankasingh2824]

Screen is entirely blur for me

[@238_vibhavsingh7]

I want my value in monthly basis from jan to Dec

[@deshn21]

Couldn't see any thing. Zoom in when you demonstrate. Also your months were in alphabetical order, which is wrong!

[@rdot980]

How do you know they are in ABC order if you can't see it? Also, it has been 10 months. Have you learned how to change that default behavior?