While on a recent project I needed to build a variation of the DAX date table. In my previous post, found here Creating DAX Date Tables I was built a date table for every day of the month for multiple years. I’ve only ever needed to consume a fully populated date calendar, but in this instance because the data I was collecting was already aggregated to the first of the month I only needed a date calendar with each month’s start date. After some playing around with my previous DAX functions I think I was able to come up with an elegant solution.
Let’s get into it.
Let’s begin by making a basic table. Open Power BI Desktop, on the Modeling ribbon click New Table.
Enter the following code:
Dates =
GENERATE (
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
This will produce our standard table from January 1st to December 31st, 2017 with every day populated in between. In, addition we get numerical values for Day, Month and Year.
Note: This table is producing a list of dates using the Calendar DAX function then iterating row by row to calculate each numerical value for the Day, Month and Year.
Add the Table visual with the following Values from our created DAX table.
Dates Table
Note: When you drag over the field labeled Date the field will be naturally added as a Date Hierarchy. To remove the Date Hierarchy, you have to click the little drop down arrow next to the word DATE and select the word Date in the drop down menu. This will remove the Year, Quarter, Month and Day date context from the visual.
The date calendar we made has every date, but we want only the first of each month.
Lets build a new table by following the previous steps and adding the following:
Start of Month Dates =
GENERATE (
GENERATESERIES(1,12),
VAR inc = [Value]
RETURN ROW (
"date", DATE(2017,inc,1)
)
)
Add the Table visual to the report page and add the following fields:
Start of Month Date Table
Note: I already removed the Date Hierarchy using the instructions listed above in the previous note.
This new DAX Date table is first generating a list of numbers 1 to 12 one for each month. Then it iterates through the list and produces a date using the Date function were we manually provide the Year, and the day. You can see the Generate function produces a column of data called [Value]. The variable denoted by VAR inc is capturing the number for each month. So, now what if we want to produce more than one year of dates? Super simple, just change the generate series from 1 to 12 to 1 to 48. This will produce three years of dates.
Change your Start of Months Dates to the following:
Start of Month Dates =
GENERATE (
GENERATESERIES(1,48),
VAR inc = [Value]
RETURN ROW (
"date", DATE(2017,inc,1)
)
)
With one number change we can produce 4 years of dates.
Four Years of Dates
Cool, let’s go a little further. Just in case we need it we can also produce a list of dates that contain the end of the month. Add the following your Start of Month Dates with the following DAX table (don’t forget the comma on line 1 in the ROW function):
We have added a new column to note the end of each month.
Date Table with Start and End Columns
Well, thanks for following along. In my use case this start of month date table was exactly what I needed. I thought this was a handy little DAX table, and I hope you have found this helpful as well. Be sure to share this post if you found this helpful.
There are many cases when you will need to create a date table within Power BI desktop. This could be as simple as creating a master date table or more complex such as creating a monthly or weekly index number tied to a date. To create a date table there are two methods for creating a date table. Method one, create the table directly in the Power BI Desktop, or method two load the date table from the data source.
For this tutorial we will walk through a couple different examples that are specifically addressing creating a date calendar via DAX expressions.
Let’s begin by making a basic table. Open Power BI Desktop, on the Modeling ribbon click New Table.
New Table
In the formula bar enter the following DAX expression:
Dates =
GENERATE (
CALENDAR ( DATE ( 2017, 1, 1 ), DATE ( 2017, 12, 31 ) ),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
This generates a simple date table. Let’s walk through what is happening here.
The CALENDAR DAX function generates a table with a list of dates from Jan 1 to Dec 31 of 2017.
We define variables (denoted by VAR) to capture details from the column named [Date] that is created by the CALENDAR function.
The Return function generates one row at a time. The row iterates through each [Date] item in the list which was created by the CALENDAR function. Variables are re-calculated for every row execution.
Note: When creating DAX tables as we are doing so in this example, the DAX table only refreshes when the report refreshes. Thus, if you want the date list to increase over time, or your using a NOW() in the DAX table you will need to be sure to schedule refreshes for the Power BI report in the PowerBI.com service.
By contrast we can also generate the same data table by calculating our data column by column. Again, on the Modeling ribbon click the New Table icon and add the following DAX:
While this is great, we have a date table now, but what we lack is flexibility and automatic time intelligence. One option to change this table to auto detect dates within your data model is to replace the CALENDAR DAX statement with CALENDARAUTO().
To use CALENDARAUTO we need to supply a table with a column of dates. We will quickly create a dummy data table with a couple of dates, so we can use CALENDARAUTIO.
Click Enter Data on the Home ribbon. Enter the following information into the Create Table screen. Click Load to add this data to the data model.
Enter Date Table
Now that we have loaded a table into the model with two dates, we can add our new date table. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 3 =
GENERATE (
CALENDARAUTO(),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
Note: In the MyData table we added two dates, 3/3/2017 and 10/30/2017. When we look at the included dates in the new Date 3 table we have every date listed from January 1 to December 31st. This is because the DAX function CALENDARAUTO will return the entire year of calendar dates even if it only finds one date within a given year period of time.
Let’s say we want to build a date calendar that will automatically grow and change over time. We want to identify today’s date and then create a list of dates for the previous year.
Moving back to generating a date table by rows we can now use the DAX NOW function. On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 4 =
GENERATE (
CALENDAR( DATE( YEAR( NOW() ) - 1, MONTH( NOW() ), DAY( NOW()) ), NOW()),
VAR currentDay = [Date]
VAR day = DAY( currentDay )
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
"day", day,
"month", month,
"year", year )
)
Note: In this DAX table we used the NOW() function which returns a date and time. The same can be done when using the TODAY() function which only returns the date and not the time.
This now generates is a date table that starts one year ago and populates all the dates until today. For example, if today is 10-29-2017, then the date list would start at 10-29-2016 and end on 10-29-2017. Pretty cool…
Let us move further down the rabbit hole. We can also start adding calculations that helps us move through date time calculations. For example, you may want to calculate this month’s total sales and possibly last month’s sales. By adding columns with an index, you can quickly shift time periods. Doing so makes time calculations much easier.
On the Modeling ribbon click the New Table icon and add the following DAX:
Dates 5 =
GENERATE (
CALENDAR( DATE( YEAR( TODAY() ) - 2, MONTH( TODAY() ), DAY( TODAY()) ), TODAY()),
VAR startOfWeek = 1 // Where 1 is Sunday and 7 is Saturday, thus a 3 would be Tuesday
VAR currentDay = [Date]
VAR days = DAY( currentDay )
VAR months = MONTH ( currentDay )
VAR years = YEAR ( currentDay )
VAR nowYear = YEAR( TODAY() )
VAR nowMonth = MONTH( TODAY() )
VAR dayIndex = DATEDIFF( currentDay, TODAY(), DAY) * -1
VAR todayNum = WEEKDAY( TODAY() )
VAR weekIndex = INT( ROUNDDOWN( ( dayIndex + -1 * IF( todayNum + startOfWeek <= 6, todayNum + startOfWeek, todayNum + startOfWeek - 7 )) / 7, 0 ) )
RETURN ROW (
"day", days,
"month", months,
"year", years,
"day index", dayIndex,
"week index", weekIndex,
"month index", INT( (years - nowYear ) * 12 + months - nowMonth ),
"year index", INT( years - nowYear )
)
)
Note: The DAX equation above will work in your report without any changes. However, I made a variable called startOfWeek. This variable allows you to define the start day of the week. For example, if you data starts a new week on Sunday, then the startOfWeek number will be a 1. If your data start of week begins on Wednesday then the start of week number would be a 4. This allows you to auto detect the day of the week and then automatically arranges all your weekly index numbers in the correct format. Try playing around with this variable to see how DAX table changes.
So why work so hard on the date table? Well by having a robust date table you can simplify many of your measures that you need to build for your report. Consider the following example:
You have a Sales table with a date and sales column.
Sample Sales Data
And you have our fancy Dates 5 Table we created earlier:
Date 5 Calendar
The Date 5 table is linked to the Sample Sales table:
Date and Sales Tables Linked
You can now build the following DAX measures inside the Sample Sales table:
The nice thing about these measures is that every time the data set refreshes the dates will automatically recalculate the last week and last five weeks.
If you want to be able to handle the additional filter context of the visual, you can pick up the visual filter context using variables (VAR). Then you can RETURN a calculate function that will shift all your time ranges for you.
Well that is about it. Thanks for following along.
I am so thankful you have taken the time to read my tutorial. My hope is that by using these free tutorials you can become a rock-star at work. In order to keep these tutorials free please consider purchasing the Power BI Desktop file for this tutorial. Come on it’s only a dollar, I mean you spent than that on your coffee this morning.
You can pay with your PayPal account or via credit card
For those of you who work in supply chain management this tutorial will be right up your alley. In my previous job position I had a lot of interaction with our shipping department. We would look at when orders were placed from the customer, and conduct a comparison to what orders were actually shipped or cancelled prior to shipment. Our analytics team would produce reports and metrics to our customers about orders and shipment information.
In an ideal world, every product ordered on the purchase order would be shipped and some point in the future. But, as we know, in the real world this isn’t always the case. Orders get cancelled, products get re-ordered, challenges happen, and therefore we would need to track all these changes. In our shipping analytics group, the team would pull data from our shipping system with columns similar to the following:
Order Date, Ship date, Product type, and Shipped QTY
Sometimes you want to sum the data by the order date, and in other cases you want a total by the shipped date.
In this example, we will walk through making a measure that uses the DAX formula USERELATIONSHIP. To learn more about this function from the Microsoft documentation follow this link.
Open PowerBI Desktop, click the Get Data button on the Home ribbon and select Blank Query. Click Connect to open the Query Editor. On the View ribbon click the Advanced Editor button. While in the Advanced Editor paste the following code into the editor window, click Done to complete the data load.
Note: If you need some more help loading the data follow this tutorial about loading data using the Advanced Query Editor. This tutorial teaches you how to copy and paste M code into the Advanced Editor.
let
Source = Excel.Workbook(Web.Contents("https://powerbitips03.blob.core.windows.net/blobpowerbitips03/wp-content/uploads/2017/06/Clothing-Sales-Ship-Order-Dates.xlsx"), null, true),
ClothingSales_Table = Source{[Item="ClothingSales",Kind="Table"]}[Data],
#"Changed Type" = Table.TransformColumnTypes(ClothingSales_Table,{{"Order Date", type date}, {"Ship Date", type date}, {"Category", type text}, {"Sales", Int64.Type}})
in
#"Changed Type"
Your loaded data should look like the following:
Sales Data Load
Click Close & Apply on the Home ribbon to load the data into the data model.
We will want to create two measures, one that performs a calculation on the Order Date column, and one on the Ship Date. To do this we need a date table to populate all the dates needed for this data set.
We can do this by creating a DAX date table.
On the Modeling ribbon click New Table.
New DAX Table
In the formula bar enter the following.
DateList =
GENERATE (
CALENDAR ( DATE ( 2012, 1, 1 ), DATE ( 2017, 12, 31 ) ),
VAR currentDay = [Date]
VAR startYear = 2012 // we know this by looking at our data
VAR month = MONTH ( currentDay )
VAR year = YEAR ( currentDay )
RETURN ROW (
“month”, month,
“year”, year,
“month index”, INT ( ( year – startYear ) * 12 + month ),
“YearMonth”, year * 100 + month )
)
Note: This DAX formula is building a date table, for each row we are building the columns, Month, Year, Month Index, and an integer for YearMonth index. This is a simple way to repeatedly create a date calendar based on your data.
Great, we have completed the data loading. Now, we need to link the date table to the Clothing Sales data. To do this click on the Relationships button on the black navigation bar located on the left side of the screen. Then Click & Drag the Date column from the DateList table to the Order Date column of the ClothingSales table. This will create a one to one relationship link between the two tables. Note that the relationship is illustrated in a solid white line. This means it is an active relationship.
Next, drag the Date column from the DateList table to the Ship Date column of the ClothingSales table. We have made our second connection. Note that this connection has dotted white line. This means this connection is not active. Also, we can observe that the relationship between the two tables, DateList and ClothingSales is a one to many relationship. This is denoted by the * on the ClothingSales table, and the (1) one on the DateList table. The * means there are duplicate values found in the ClothingSales table. The (1) on the DateList table means in the Date column we only have unique values, no duplicates.
Note: You can edit the connections between tables by double clicking the connecting wires. This brings up the Edit Relationship dialog box which allows you to edit things like, the Cardinality, Cross Filter Direction and activating / deactivating the connection.
Once you’re done your relationships should look like the following:
Linked Tables
By default, Power BI will only allow one active connection between tables. Therefore, we have one connection active and the other has been inactivated by default. Return to the report view by clicking the Report icon on the left black navigation bar.
Report View
Now that we have completed the data modeling let’s make some visuals. We will start by making a simple table to see what the data is doing. Add the columns from the two tables, ClothingSales and DateList to a Table Visual.
Create Table Visualization
Great! Now we have the total number of sales based on the order date. We know this because it is the primary connection that we established earlier when we linked our two tables together. But, what if I wanted to know the sales that were shipped based on the Ship Date. Earlier we made this connection but it is inactive.
Here is the awesomeness! We can create a measure that calculates different results between a user specified relationship.
First, we will re-calculate the sales number that we already have in our table. On the Home ribbon click the New Measure and enter the following in the DAX formula bar:
Order Date Sales =
CALCULATE (
SUM ( ClothingSales[Sales] ),
USERELATIONSHIP ( ‘DateList ‘[Date], ClothingSales[Order Date] )
)
Note: In this DAX formula we are creating a explicit measure, meaning we are specifically telling Power BI to sum a column. An implicit calculation is what we did earlier when we added the sales column to the table.
The USERELATIONSHIP filter within the calculation forces Power BI to calculate the sum based on the dates listed in the Order Date column. To see another demo on UseRelationship you can watch this video from Curbal.
Create another measure with the following DAX formula:
Ship Date Sales =
CALCULATE (
SUM ( ClothingSales[Sales] ),
USERELATIONSHIP ( ‘DateList ‘[Date], ClothingSales[Ship Date] )
)
This time we are forcing Power BI to use the inactive relationship to calculate the sum of the sales by shipped date. Add the two new measures to our table and we now can see how the calculations differ.
Sales By Order & Ship Date
The calculated sales for the order dates match our earlier column. This is expected, and we can confirm that this calculation is working properly. The shipped date sales are now calculating a different number. In some cases, the Shipped Date Sales is lower than the orders, because in that month you took in more orders than you shipped. In other months, the Shipped Date Sales is higher than the Order Date Sales, because there were likely large shipments ordered in the prior month and shipped in a different month.
By adding a Bar Chart from the Visualizations pane, we can now see sales by order date and ship date.
Bar Chart by Year
We can even dig deeper into the data. Click the Expand button to see the data by Year and Month.
Expand Button
Well that is about it. I hope you enjoyed this tutorial about using two relationships between data tables. If you want more information about DAX check out these books that I have found extremely helpful.
Often you will need to create some custom calendars within your PowerBI reports. Ruth Pozuelo from Curbal does a great video tutorial on using Calendar() and CalendarAuto(). I have use the Calendar() DAX function many times and find it very helpful. The following videos are built directly within DAX. This approach is one of many different methods that can be used to generate a list of dates. In a previous tutorial I talked about how to build a date table within the Query Editor (build date table in the Query Editor).
One method that Ruth talks about is the ability to use the CalendarAuto(). I have not used this expression in any previous reports, but seeing how simple it is to implement this will definitely have to be added to the toolbox.
Curbal has been generating a lot of great content. To learn about for more information you can visit the website found here, or visit the YouTube Channel.
For more great videos about Power BI click the image below:
