Tag: Date table

  • 1 easy way to connect SharePoint to Power BI – Part 2

    1 easy way to connect SharePoint to Power BI – Part 2

    SharePoint to Power BI – Part 2

  • Demystifying Date Dimension Part 3

    Demystifying Date Dimension Part 3

    Hello All, let us take a deep dive into Date Dimension Part 3


    However, for the next few blog posts, we will focus on M Query, and we will first work with static values and then modify the M Query to make it more dynamic.

    Steps that you can follow along with me are as follows: 

    We are using the sample data from the previous post. You can download the sample file and have a look at the first 2 parts of this series here and here.

    superstore_sample_dataDownload

    Click on the edit queries/transform data.

    Click on the new source and click on a blank query.

    This should look something like this.

    In the formula bar type in = List.Dates(#date(2020,1,1), 365, #duration(1,0,0,0)) and press enter.

    This should have given you something like this.

    I hope you recall the three main components that I spoke about, StartDate, EndDate, and the Duration from previous posts. Once again, I will reiterate that this is all that we need for any date table. We get this is all about manipulating the code, but eventually, that is all we need to create a date table.

    Based on this let us break this formula and understand each component of it.

    • The List.Date function takes in three parameters, start as date, count as the number, and duration as the increment.
    • The start is the beginning date or the date when my calendar should start. In this example, I am using the beginning of this year as the start.
    • The count is the number or how many such date instances you want to have on this list. I am choosing 365 as my count. Be careful that this count is the number of days. It is for several instances. If in the duration we choose hours, then this instance will mean 365 such hour instance.
    • The step is the last parameter, and it simply means what the duration or increment of each instance is. I am calling the function #duration and saying that I need an increment of a day, #duration(1,0,0,0).
    • Since we have used the function List.Date, this Query1 is a list.

    Next, click on Transform and convert into a table and rename this as a query as MyCal. 

    When you are prompted for the To Table dialog box, you can hit ok as there is no delimiter we need to worry about.

    Next, rename this column to Date by double-clicking it and type in the word Date. Alternatively, you can also right click this column and rename it. While you are at it, click on the small icon ABC/123 to change the data type to Date. 

    At this point, you have a column called Date in the table called MyCal and the date ranges from 01/01/2020 to 12/30/2020.

    But wait, what happened to 31st??? Remember, we chose the count as 365 earlier. Hence it took 365 days from the StartDate of 01/01/2020.

    Do not worry as we will modify the code in a short while to include the last day of the year.

    Let us continue expanding this table.

    • Click on Add Column in the ribbon.
    • Click on the down arrow under the Date icon.
    • Click on Year, and this will add a new column called Year.
    • Similarly, we can add Month, Quarter, Week, and Day columns in our table. 

    At this point, we should have a decent date table. However, this is very static, and we still have not taken care of the 31st of this year. To take care of that, change the count to 366, and you should have all the days of this year.
    But this is very brutish (because in the Year 2021, we will have 365 days instead of 366). In the next blog, we will make this table more dynamic.

    The complete M code is as follows, to get the same result as above, click on the new blank query and click on the advanced editor, and past the following.

    let
    Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
    #”Converted to Table” = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #”Renamed Columns” = Table.RenameColumns(#”Converted to Table”,{{“Column1”, “Date”}}),
    #”Changed Type” = Table.TransformColumnTypes(#”Renamed Columns”,{{“Date”, type date}}),
    #”Inserted Year” = Table.AddColumn(#”Changed Type”, “Year”, each Date.Year([Date]), Int64.Type),
    #”Inserted Month” = Table.AddColumn(#”Inserted Year”, “Month”, each Date.Month([Date]), Int64.Type),
    #”Inserted Quarter” = Table.AddColumn(#”Inserted Month”, “Quarter”, each Date.QuarterOfYear([Date]), Int64.Type),
    #”Inserted Week of Year” = Table.AddColumn(#”Inserted Quarter”, “Week of Year”, each Date.WeekOfYear([Date]), Int64.Type),
    #”Inserted Day of Year” = Table.AddColumn(#”Inserted Week of Year”, “Day of Year”, each Date.DayOfYear([Date]), Int64.Type)
    in
    #”Inserted Day of Year”

    This should give you a final result like below.

    In the next post, we will continue with the same date table to make it dynamic. If you have questions/queries/comments, please do not hesitate to reach out to me.

  • Demystifying Date Dimension Part 4

    Demystifying Date Dimension Part 4

    Hi all, in this post, let us make a dynamic date dimension.

    If you followed me every step of the way, you should already have this on your table. If not, click on the blank query and paste the following code to speed up and read about the previous post here, and download the sample file below.

    superstore_sample_dataDownload
    let
        Source = List.Dates(#date(2020,1,1), 366, #duration(1,0,0,0)),
        #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
        #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
        #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}})
           
    in
        #"Changed Type"

    Now that we are on the same page, let us begin!!!
    I hope you still remember the three main requirements for a date table. I know I have said this over and over, but once again I will reiterate this that to have a date table, we need StartDate, EndDate, and Duration.

    Now with this in mind, let us begin. First, we need to find the StartDate. In this case, it is 01/01/2020.

    Click on the function icon (fx) and insert a new step.

    By default, you should see that this new step is called Custom 1 and that it is referencing the previous step, as shown below. In my case, it is referencing the step called Inserted Day of Year; for you, it could be different. But the main point here is whenever we add a new step, then by default, it will always refer to the immediate prior step.

    Delete everything including the ‘=’ sign and type in 01/01/2020 and while you are at it, rename this step (right-click on the Custom 1) to StartDate.

    This will fetch us the StartDate. Since now we have StartDate, go ahead and replace #date(2020, 1, 1) in the source step with StartDate. 

    Now we need a count of days. For this let us dynamically get the count of days from 01/01/2020 till today. 

    Create a new step and input this DateTime.Date(DateTime.LocalNow()) in this formula bar and rename this step as EndDate.

    This nested function first fetches the local/current date in DateTime format, then we extract just the date component from it. 

    Insert another step, and this time we will invoke Duration. Days function and wrap it around EndDate minus StartDate. This time we are subtracting the StartDate from EndDate and fetching the number of days from it. (If you want to include today, then we must add 1 to it so we can have today counted as well). Let us rename this new step as Count.

    At this point, we have StartDate and Count. Let us navigate back to the source step and replace the hardcoded 366 with Count. 

    Now insert another step and reference it back to the Changed Type step in the applied steps as shown below. By default, every new step references the immediate prior step, but we want to reference another step hence, the need to manually tell M query where to look for. 

    Once we have the above step, all that is needed is to extract relevant date parts as we did in the previous blog. 

    At this point, our Date Table is ready. We can use this code and just by changing the StartDate we can generate all the dates till today. I changed my StartDate to 01/01/2010 and I can now see all the dates from 01/01/2010 till today.

    If you noticed, we had to enter the StartDate manually, but what if we wanted to use the earliest date present in our model. Well, it is straightforward to do so.

    In our sample, we have an Orders table. Let us say that we wanted to use the first Order Date as my StartDate.
    We can call another List function called List. Min and pass that to our source. Let us see that in action.

    For the sake of simplicity and comparison, I am going to insert another step right after StartDate. Since we know that every new step in M Query references the immediate prior step, inserting a new step right after StartDate will not break our code.

    Insert a new step after StartDate and rename as MinDate.

    • Since we want the earliest date from Orders Table, call List.Min function on the Order Date column.
    • For the MinDate step, insert this ” = List.Min((Orders[Order Date]))”. This will look for the earliest date in the column called Order Date in the table called Orders. 

    Once we have this, go back to the source and replace StartDate with MinDate. 

    At this point, you should have something like below. Here the date table starts from 01/01/2015 which is the first order date in the Orders table. 

    Lets us now say, that we wanted the EndDate to be the last ship date. This is also equally easy. Follow these steps and you will have a true dynamic date table whose dates are based on the dates in your model.

    1. Insert a new step after EndDate and rename it as MaxDate.
    2.  Since we want the latest date from Orders Table, call List. Max function on the Ship Date column.
    3. For the MaxDate step, insert this ” = List.Max((Orders[Ship Date]))”. This will look for the latest date in the column called Ship Date in the table called Orders. 

    At this point, you should have something like below. Here the date table ends on 07/07/2015 which is the second last ship date in the Orders table. To get the last ship date, just add 1 to the count step as follows. 

    This way we can create a dynamic date table that fetches the StartDate and the EndDate using the data within our model.

    I am including all the variations of the M code for easy reference. 

     let
    Source = List.Dates(MinDate, Count, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    // We can manually choose the Start Date and the rest of calendar will populate based on this value
    StartDate = #date(2010, 1, 1),
    // this is taking the earliest date from Order Date column. 
    MinDate = List.Min((Orders[Order Date])),
    // this is taking end date as today's date
    EndDate = DateTime.Date(DateTime.LocalNow()),
    // this is taking the last Ship Date 
    MaxDate = List.Max((Orders[Ship Date])),
    Count = Duration.Days(MaxDate - MinDate) + 1,
    Custom1 = #"Changed Type",
    #"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([Date]), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Week of Year", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Sorted Rows" = Table.Sort(#"Inserted Day of Year",{{"Date", Order.Descending}})
    
in
    #"Sorted Rows"

    I hope you liked demystifying date dimension series and please do let em know you thoughts on this. If you have a request, please do not hesitate to reach out.

    Till next time !!!

  • Power BI Date Dimension Demystified Part 2

    Power BI Date Dimension Demystified Part 2

    Hello All, let us continue with our journey of demystifying the Date Dimension.

    In my last post, we learned to use CALENDARAUTO() function. We will continue today with the second DAX function called Calendar(). We will also look at how to extract other “relevant columns” such as year, month name, month number, etc. I will go over the following in this post.

    1. Create a date column using the CALENDAR() function.
    2. Use multiple DAX formulas to extract/add these columns.
    3. Use one DAX formula to generate the entire Date Table that will include all these columns.
    4. Dynamic dates selection and usage.

    CALENDAR() Function:

    The CALENDAR() functions take two parameters, StartDate, End Date, and return a table with one column with all dates between the StartDate and EndDate.

    For this exercise, let StartDate be 01/01/2020 and EndDate be 12/31/2020.

    If you follow this series from Part 1, then your environment is already set, and follow along. If you are new and want to follow the same dataset, please go through Part 1 and the Power Query. 

    Let us begin by navigating to the Modeling tab, click on the New Table, and use the following code to create a column in this table containing all the dates between the start and end date as shown below.

    date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

    Once you hit enter, you should have the Date Column in your model. To view it, click on the data tab on the left, and you should be a single column in this table of Date/Time datatype ranging from 1/1/2020 to 12/31/2020 as shown below.

    As you can see we have a very similar result to what we had in Part 1. Since in Part 1 we used CALENDARAUTO(), Power BI fetched the minimum (start-date) and maximum (end-date) automatically from our model. However, for the CALENDAR() function, we need to specify the StartDate and EndDate.

    Once we have this column, we can then extract important and relevant information regarding the date. We will be extracting information such as Month Number, Year, Month Name, Weekday, Quarter, Second approach to Quarter, Week Number, Day Number of the Month, WeekDay Number of the week.

    I have included all these code snippets as follows to follow along with me as I do this. After the Date Column is created (using the approach above), all we need to do is create a new column for each of the relevant details.

    To create the Month Number: 

    1. Click on the Data tab on the left on the Power BI Desktop.
    2. Once you are in the Data tab, you should see the date table we created above on the right under the field. 
    3. If you followed along, then you should have a table named “date table using the calendar.”
    4. Click on the new column icon.
    5. Enter the following formula in the formula bar. 
    6. Month Number = Month(‘date table using calendar'[Date])
    7. Press enter.
    8. Your new column named Month Number is ready.
    9. Follow the same process for Month Name, Year, Weekday, Quarter, Quarter Second Approach, Week Number, Day Number, Weekday Number.

    Use the following code snippets to generate these columns. I have included the images of the next two columns as an example. 

    We have already done the first and second below. 

    • date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))
    • Month Number = MONTH(‘date table using calendar'[Date])
    • Month Name = FORMAT(‘date table using calendar'[Date], “mmmm”)
    • Year = YEAR(‘date table using calendar'[Date])
    • Weekday = FORMAT(‘date table using calendar'[Date], “dddd”)
    • Quarter = Var monthnum = MONTH(‘date table using calendar'[Date])

    Return 

    SWITCH

    (

                        TRUE(),

                        monthnum <=3, “Q1”,

                        monthnum > 3 && monthnum <= 6, “Q2”,

                        monthnum > 6 && monthnum <= 9, “Q3”,

                        monthnum > 9, “Q4”

    )

    • Quarter Second Approach = 

    // storing the month number in a variable called mnth

    var mnth = MONTH(‘date table using calendar'[Date])

    // getting the quarters 

    var myqrt = ((mnth -1)/3 ) + 1

    // truncate this result using Trunc function and WITHOUT passing the optional params

    var trnc = TRUNC(myqrt)

    // concat the alpahbet Q to this result 

    var addQ = “Q ” & trnc

    // Retun this

    return addQ

    • Weeknumber = WEEKNUM(‘date table using calendar'[Date])
    • Day Number of the month = DAY(‘date table using calendar'[Date])
    • Weekday number of the week = WEEKDAY(‘date table using calendar'[Date])

    You should have the following columns in your date table by now.

    Let us take a pause here and create these columns using the Part 1 approach. In that post, we used Calendarauto() function. Using the same approach let us create other columns. 

    1. Once again navigate to the Data tab. 
    2. Click the table named date table from calendarauto. 
    3. Click on the new column and enter the code snippets 

    These are the code snippets for the CALENDARAUTO() function. Please note that the start date and end date will be different for both these functions. The reason being in CALENDAR() we are specifying the StartDate and EndDate, whereas in CALENDATAUTO() it identifies the start date and end date based on the model. We have covered the CALENDARAUTO() in detail in the previous post here.

    // all the columns for the Date Table from the start

    • date table from calendarauto DAX = CALENDARAUTO()
    • Month Number = MONTH(‘date table from calendarauto DAX'[Date])
    • Month Name = FORMAT(‘date table from calendarauto DAX'[Date], “mmmm”)
    • Year = YEAR(‘date table from calendarauto DAX'[Date])
    • Weekday = FORMAT(‘date table from calendarauto DAX'[Date], “dddd”)
    • Quarter = Var monthnum = MONTH(‘date table from calendarauto DAX'[Date])

    Return 

    SWITCH

    (

                        TRUE(),

                        monthnum <=3, “Q1”,

                        monthnum > 3 && monthnum <= 6, “Q2”,

                        monthnum > 6 && monthnum <= 9, “Q3”,

                        monthnum > 9, “Q4”

    )

    • Quarter Second Approach = 

    // storing the month number in a variable called mnth

    var mnth = MONTH(‘date table from calendarauto DAX'[Date])

    // getting the quarters 

    var myqrt = ((mnth -1)/3 ) + 1

    // truncate this result using Trunc function and WITHOUT passing the optional params

    var trnc = TRUNC(myqrt)

    // concat the alpahbet Q to this result 

    var addQ = “Q ” & trnc

    // Retun this

    return addQ

    • Weeknumber = WEEKNUM(‘date table from calendarauto DAX'[Date])
    • Day Number of the month = DAY(‘date table from calendarauto DAX'[Date])
    • Weekday number of the week = WEEKDAY(‘date table from calendarauto DAX'[Date])

    Awesome !!! we now have a date table with relevant data using both Calendar and Calendarauto approach. Let us dig a little deep into these DAX formulas that we have written. If you notice, we created multiple calculated columns based on the very first date column. This is okay to do so. However, we can combine all these separate DAX formulas into one.

    For this let us create an entire date table using a single DAX formula and adding these columns within it. 

    1. Click on the modeling tab.
    2. Click on the new table icon.
    3. Paste the following code snippet.
    4. Once you press enter, your entire date table will be ready and populated. 

    Date Using DaxCal Calendar =

    // This variable is storing the column values

    VAR DaxCal =

       CALENDAR ( DATE ( 2020, 01, 01 ), DATE ( 2020, 12, 31 ) )

    RETURN

       // AddColumns returns a table with new columns specified by the DAX expressions

       ADDCOLUMNS (

         DaxCal,

         “Year”, YEAR ( [Date] ),

         “Month Number”, MONTH ( [Date] ),

         “Month Name”, FORMAT ( [Date], “mmmm” ),

         “Quarter”,

           “Q “

             & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

         “WeekNum”, WEEKNUM ( [Date] ),

         “Day of the Month”, DAY ( [Date] ),

         “Weekday Number of the Week”, WEEKDAY ( [Date] ),

         “Weekday Name”, FORMAT ( [Date], “dddd” ),

         “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

       )

    You should have a date table as shown below.

    Let us break down the code.

    1. We are calling Calendar Function just like we did before. However this time we are storing the result in a Variable.
    2. We then want to return a table with other columns created using the DAX expressions.
    3. To do so we are calling the ADDCOLUMNS function.
    4. The first parameter we need to pass is the name of the table. In this case, it is our variable. I called it DaxCal.
    5. The next parameter is the name of the column. I called it Year.
    6. The last parameter is the DAX expression. In this case, it is YEAR([DATE]).
    7. Follow the same process till all the other columns have been called.

    I have included the very similar code snippet for CALENDATAUTO() function that was used in Part 1. 

    Date Using DaxCal Calendarauto = 

    VAR DaxCal =

       CALENDARAUTO ()

    RETURN

       ADDCOLUMNS (

         DaxCal,

         “Year”, YEAR ( [Date] ),

         “Month Number”, MONTH ( [Date] ),

         “Month Name”, FORMAT ( [Date], “mmmm” ),

         “Quarter”,

           “Q “

             & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),

         “WeekNum”, WEEKNUM ( [Date] ),

         “Day of the Month”, DAY ( [Date] ),

         “Weekday Number of the Week”, WEEKDAY ( [Date] ),

         “Weekday Name”, FORMAT ( [Date], “dddd” ),

         “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )

       )

    Though we have created a Date Table, we are not done yet. Let us improve on this approach. What we just did is what I call the brute force method of creating a Data Table using the Calendar function. What I mean by this is that we hardcoded the StartDate and EndDate values in this.

    Let us take this further and make it dynamic. If you remember from my previous post, There are 3 Main Things we need. StartDate, EndDate, and Duration. Once again I will reiterate that please remember this as this will help us understand the concept of Date Table when we create the Date Dimension using M Query in the future posts.

    By now we know that the function CALENDAR() requires StartDate and EndDate. For now, we handed it by providing static values. Let us make it more dynamic.

    Let us say, that we needed our date table to start from the first Order date as the StartDate and the last ship date as the EndDate.

    To do so we will declare two more variables as follows.

    Date Using DaxCal Calendar Dynamic =
    // we are getting the minimum of the Order Date and storing it in a variable
    Var MinDate = MIN(OrdersData[Order Date])

    // we are getting the latest Ship Date and storing it in a variable
    Var MaxDate = MAX(OrdersData[Ship Date])

    // Calling the Calendar function and passing into it, the two variables as the arguments and returning the result with relevant columns
    VAR DaxCal =
    CALENDAR(MinDate,MaxDate)
    RETURN
    ADDCOLUMNS (
    DaxCal,
    “Year”, YEAR ( [Date] ),
    “Month Number”, MONTH ( [Date] ),
    “Month Name”, FORMAT ( [Date], “mmmm” ),
    “Quarter”,
    “Q “
    & TRUNC ( ( ( MONTH ( [Date] ) – 1 ) / 3 ) + 1 ),
    “WeekNum”, WEEKNUM ( [Date] ),
    “Day of the Month”, DAY ( [Date] ),
    “Weekday Number of the Week”, WEEKDAY ( [Date] ),
    “Weekday Name”, FORMAT ( [Date], “dddd” ),
    “Full Date Only”, FORMAT ( [Date], “mm/dd/yyyy” )
    )

    With this we were able to use two DAX functions, CALENDAR() and CALENDARAUTO(), to create the date table with additional columns as needed. I hope that I was able to shed some light on these two DAX functions. In the file below you will find all the code snippets that we used thus far.

    All the columns for the Date Table using CALENDAR()

    // all the columns for the Date Table from the start -- CALENDAR()


date table using calendar = CALENDAR(DATE(2020,01,01), DATE(2020,12,31))

Month Number = MONTH('date table using calendar'[Date])

Month Name = FORMAT('date table using calendar'[Date], "mmmm")

Year = YEAR('date table using calendar'[Date])

Weekday = FORMAT('date table using calendar'[Date], "dddd")


Quarter = 
            Var monthnum = MONTH('date table using calendar'[Date])
            Return 
                SWITCH
				(
				
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table using calendar'[Date])

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ



Weeknumber = WEEKNUM('date table using calendar'[Date])

Day Number of the month = DAY('date table using calendar'[Date])

Weekday number of the week = WEEKDAY('date table using calendar'[Date])

    All the columns for the Date Table using CALENDARAUTO()

    
// all the columns for the Date Table from the start -- CALENDARAUTO()


date table from calendarauto DAX = CALENDARAUTO()

Month Number = MONTH('date table from calendarauto DAX'[Date])

Month Name = FORMAT('date table from calendarauto DAX'[Date], "mmmm")

Year = YEAR('date table from calendarauto DAX'[Date])

Weekday = FORMAT('date table from calendarauto DAX'[Date], "dddd")

Quarter = 
            Var monthnum = MONTH('date table from calendarauto DAX'[Date])
            Return 
                SWITCH
				(
                    TRUE(),
                    monthnum <=3, "Q1",
                    monthnum > 3 && monthnum <= 6, "Q2",
                    monthnum > 6 && monthnum <= 9, "Q3",
                    monthnum > 9, "Q4"
                )


Quarter Second Approach = 

// storing the month number in a variable called mnth
var mnth = MONTH('date table from calendarauto DAX'[Date]
)

// getting the quarters 
var myqrt = ((mnth -1)/3 ) + 1

// truncate this result using Trunc function and WITHOUT passing the optional params
var trnc = TRUNC(myqrt)

// concat the alpahbet Q to this result 
var addQ = "Q " & trnc

// Retun this
return addQ


Weeknumber = WEEKNUM('date table from calendarauto DAX'[Date])

Day Number of the month = DAY('date table from calendarauto DAX'[Date])

Weekday number of the week = WEEKDAY('date table from calendarauto DAX'[Date])

    In the next blog post, we will create Date Dimension using Power Query. If you have questions/queries/comments please do not hesitate to reach out to me.

    Till next time !!!!

  • Power BI Date Dimension Demystified Part 1

    Power BI Date Dimension Demystified Part 1

    Hello All, today, let us take the first step towards demystifying the Date Table journey. Date Dimension plays a vital role.

    Almost always, there is a need to perform and derive time-based insights. Few ground rules must be adhered to when it comes to creating a robust date table. These are :

    • The date table must contain unique values
    • The date table must contain no null values
    • The date table must contain contiguous date values (from beginning to end)

    This series of articles will go over different manipulations/iterations of how one can achieve this.

    For any date table, we must have 3 values/properties present: the start date, the end date, and the duration. For these articles, let us call them 3P, as we will be revisiting them.

    The start date — This the first (earliest) date available. We can either enter this manually (using the calendar() function — coming up in the next article) or calculated it based on the model. 

    The end date — This is the last (latest) date available. We can either enter this manually (using the calendar() function — coming up in the next article) or calculated it based on the model. 

    The duration — This is the increment or step. Almost always, this increment is “day.” For example, let us consider the month of June 2020. 

    The start date would be 06/01/2020 — 1st of June 2020

    The last date would be 06/30/2020 — 30th of June 2020

    The duration would be each day. Therefore if we were to create a date-table, we should have 30 unique dates for June 2020. 

    Let us begin with the CALENDARAUTO() DAX function. 

    This function returns a table with a single column named “Date” containing a contiguous set of dates. The range of dates is calculated automatically based on data in the model. 

    This function has some considerations/rules associated with it, they are :

    1. The model MUST contain date-time values. 
    2. It does not take into account the date values based on the calculated table and calculated column.
    3. The earliest date is calculated based on the MinDate 
    4. The latest date is calculated based on the MaxDate 
    5. If we pass an argument (an integer between 1 and 12), then our date table range “shifts” by that step. 

    Let us begin with our sample data. 

    superstore_sample_dataDownload

    My other post (Power Query) talks about how to connect to this data. I will go over this very quickly once again. But if you need to revisit Power Query, click the icon below to do so.

    1. Click get data
    2. Click on Excel
    3. Navigate to the folder where the excel file resides
    4. Load the data (usually, we would go with transform data as this is a good practice to ensure the quality is consistent. However, this sample data is the right format as we need it to be in). 
    5. Navigate to the modeling tab and click on the new table
    6. This should open the formula bar.
    7. Name the table (I named it date table from calendarauto DAX and type in calendarauto(). My final formula is date table from calendarauto DAX = CALENDARAUTO() )
    8. This will create a date-table with a single column called date, as seen below.
    9. As you can see, it took the MinDate, MaxDate, and duration automatically based on the rules above. 
    10. We did not need to pass any parameter as, by default, the parameter (fiscal year-end month) is 12 (December)
    Power query navigation

    Now let us supply 3 as the argument to our function and see the “shift” in action. 

    1. Navigate to the modeling tab and click on the new table
    2. This should open the formula bar.
    3. Name the table (I named it date table from calendarauto with 3 and type in calendarauto(3). My final formula is the date from calendar auto with 3 = CALENDARAUTO(3) )
    4. This will create a date-table with a single column called date, as seen below.
    5. As you can see, it took the MinDate, MaxDate, and duration automatically based on the rules above. 
    6. We passed 3 as a parameter, and hence the MinDate became the end of 3rd month for the year before. Our original date started from 1/1/1995 and ended on 12/31/2025; we shifted the start date by providing 3 as the parameter. As per our rule discussed earlier, the MinDate is calculated based on the fiscal year-end month. 
    7. The fiscal year-end month is nothing but the parameter that we provide. Since, by default, the parameter (fiscal year-end month) is 12 (December) hence the MinDate was 1/1/1995. In this case, we provided 3 as the parameter, so our start date should become 4/1/1994, and keeping the reference intact, the end date is the end of March, i.e., 3/31/2026

    Our work here is not done yet. We have just a single column with date-time values. However, this is by far the most important piece if creating the entire date table. Once we have unique, non-null, date-time values (remember our rules above), we can extract all the other information out of it.

    However, this is all we need to extract all the other information out of it. The steps to extract the various values such as month name, month number, day number, etc., will be covered in the next post when we demystify the calender() function since extracting the information is the same. 

    I hope that I was able to demystify this for you. However, if you have any concerns/queries/doubts or feel that I have made an error about this, please do not hesitate to leave a comment or send me the feedback. I will be more than happy to discuss this. 

My journey from Datum to Data