Skip to main content

How to Create a Date Table in Few Steps Using Power BI

Calendar Dimension or Calendar Table is one of the crucial table in a Power BI model. I never have seen any data warehouse data model which does not have a Calendar table so far in my development. Because, when it comes to data warehousing or dimension modeling you may storing various business processes or events as Facts. So those events anyway occur in particular date or time. So simply there wouldn’t be a Power BI data model without a date table.



In this post, I’m going to share with you how to create a date table within few steps. Actually, there are two DAX measures which we can use to create a date table. Calendar(DAX) and CalendarAuto(Auto).

Calendar(DAX) = You can pass start date and End date as parameters you need to create date table.
CalendarAuto(DAX) = You can use this function without passing any parameter. Then it will generate the dates based on your data model dates. 

You can simply copy the below DAX code and paste in your Power BI Desktop DAX editor by selecting New Table in the Modeling tab.

In the code, there are two variables called Date Start and DateEnd which can change the values based on your requirement.

Calendar =
VAR DateStart =
    DATE ( 2000, 1, 1 ) -- Calendar Start Date. Change this as per your requirement.
VAR DateEnd =
    DATE ( 2050, 12, 31 ) -- Calendar End Date.
VAR CalTab =
    ADDCOLUMNS (
        CALENDAR ( DateStart, DateEnd ),
        "DateKey", FORMAT ( [Date], "YYYYMMDD" ),
        "Year", YEAR ( [Date] ),
        "MonthNumber", FORMAT ( [Date], "MM" ),
        "YearMonthNumber", FORMAT ( [Date], "YYYY-MM" ),
        "YearMonthShort", FORMAT ( [Date], "YYYY-mmm" ),
        "MonthNameShort", FORMAT ( [Date], "mmm" ),
        "MonthName", FORMAT ( [Date], "mmmm" ),
        "IsWeekDay", IF ( WEEKDAY ( [Date] ) = 7, "No", IF ( WEEKDAY ( [Date] ) = 1, "No", "Yes" ) ),
        "DayOfWeekNumber", WEEKDAY ( [Date] ),
        "DayOfWeek", FORMAT ( [Date], "dddd" ),
        "DayOfWeekShort", FORMAT ( [Date], "ddd" ),
        "Quarter", "Q" & FORMAT ( [Date], "Q" ),
        "YearQuarter", FORMAT ( [Date], "YYYY" ) & "-Q"
            & FORMAT ( [Date], "Q" )
    )
RETURN
    CalTab

Create the Calendar Table using above code 


Thanks for reading this post. Please share your thoughts on this in the comment section below. 
Labels:

Comments

  1. UnknownJuly 3, 2018 at 3:08 AM

    Thanks for sharing the descriptive information on Tableau tutorial. It’s really helpful to me since I'm taking Tableau training. Keep doing the good work and if you are interested to know more on Tableau, do check this Tableau tutorial.:-https://www.youtube.com/watch?v=y8hg5OfCeZQ

    ReplyDelete
    Replies
    1. NisalJuly 14, 2018 at 9:55 PM

      Thanks Deva for the comment. This encourage me to write more and more articles like this. :-)

      Delete
  2. UnknownJuly 12, 2018 at 12:02 AM

    Great post dear. It definitely has increased my knowledge on Tableau. Please keep sharing similar write ups of yours. You can check this too for Tableau tutorial as i have recorded this recently on Tableau. and i'm sure it will be helpful to you.https://www.youtube.com/watch?v=Ny6h82Qy4tA&t=34s
    https://www.youtube.com/watch?v=Ny6h82Qy4tA&t=34s

    ReplyDelete
    Replies
    1. NisalJuly 14, 2018 at 9:54 PM

      Thanks for the comment. This encourage me to write more and more articles like this. :-)

      Delete
  3. UnknownJuly 12, 2018 at 12:15 AM

    Well done! It is so well written and interactive. Keep writing such brilliant piece of work. Glad i came across this post. Last night even i saw similar wonderful Tableau tutorial on youtube so you can check that too for more detailed knowledge on Tableau.https://www.youtube.com/watch?v=Ny6h82Qy4tA&t=34s

    ReplyDelete
  4. NisalJuly 14, 2018 at 9:55 PM

    Thanks Kajal for the comment. This encourage me to write more and more articles like this. :-)

    ReplyDelete
  5. rmouniakOctober 22, 2018 at 10:43 PM

    It is nice blog Thank you provide important information and I am searching for the same information
    Tableau Online Training

    ReplyDelete

Post a Comment

Popular posts from this blog

Run T-SQL Script Files Using Command Line

Most of the time when we need to execute a SQL script or statement, then we go for SQL Server Management Studio to execute our T-SQL scripts. But the same result we can achieve using Command Prompt. This is very simple way to do that just a matter of write a command and pass few arguments. You can see the process status like as in when you execute a query in Management studio. Use-case : There may be having some situations you won't be able to execute T-SQL script using Management Studio. Most of the time when doing data population. In my case, I had a situation I needed to populate a Database without using backup/restore. Once I generated script with all the schemas and related data it took around 400+Mb sql file.  SQL Server Management Studio will not allow you to run the script unless you execute the query in high-end box. Because, when the file loaded to the memory it will too hard to cater our requirement. You may probably get  memory ...
Post a Comment
Read more

Forecast Financial Data Using MS Excel 2016

With the New release of Excel 2016  Microsoft has provided handy way to do the forecasting using the user data. Within few clicks you will be able to do the forecasting without knowing the thorough knowledge about the Data Mining or Predictive Analytics. Just to have some domain knowledge about the data you are going to play around will be enough. From this blog post I will be showing a sample data forecast analysis I've done using MS Excel 2016 Forecast Feature. 1. Created a Test Data Set for do the prediction. I used vTimeSeries   view in AdventureWorksDW2014 sample database. (This view returns the Time Series  data Sales data which change over the time. This can be useful to do the Financial forecast on sales data) 2. In this view return the sales amount correspond to the reporting date. I've made a small change to the query in-order to improve the readability. I sort the data-set using reporting date. 3. Open Microsoft Excel 2016 and Create a N...
8 comments
Read more