Articles

Creating Power BI Date Tables by Marking as Date Table

In a previous post, I wrote about the fact that Power BI did not have the capability to Mark a table as date, but that has changed! Now if you want a date table to use numeric keys to join to other tables you can and still get the time dimension functionality to work.  Now there is no need to use some kind of a work around to get a date table, you can just make it one.

Marking a Date Table in Power BI

Naturally there is more than one way to mark a table as a date table in Power BI.  The first way is to click on the ellipse (you know the three dots) next to the date table Name  in the Power BI Fields, which I highlighted in yellow. The menu contains an option to Mark as date table.  If you select say the locations table, Power BI is smart enough to know that this is not a date table and the option will not appear. The first time that you select the table it will show the window shown above and prompt you to select the column in the table which is an actual value.  Once you select the OK button, the menu item will have a check mark next to the Mark As Date Option and if you select it again you will see this window. Generally speaking there is no reason to Unmark a date table, but if you want to, you can.

The other place in Power BI where you can Mark a Date table is in the report view there is an option in the Modeling tab to Mark as Date Table. Note this option will be disabled unless you have clicked on a table first.  The icon is only available in the Modeling tab of the report view.  If you are in the grid view or the relationship view you won’t see the option to Mark as Date Table, it is only available in the Report Modeling tab.

Time Dimension Functions: The reason for marking a Date Table

For those of you who may be wondering: Why would I want to mark a table as a date anyway there is a very good reason.  Marking a table as a date allows you to use all of the cool features which Microsoft added to DAX to do date calculations for you.  While it is possible to not use any time based DAX functions, unless you are rather expert at DAX, like these guys, you probably will want to use the nice DAX functions Microsoft created for handy things like TOTALYTD or SAMEPERIODLASTYEAR.  Now just because you have a date table added does not mean that you can use any date in those calculations, you need to reference a table that you have marked as date, not a date field within the table.  That means that  CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(DateTime[DateKey]))  will return the correct value and CALCULATE(SUM(ResellerSales_USD[SalesAmount_USD]), SAMEPERIODLASTYEAR(ResellserSales[DateKey]))  will not.  You need to reference the table marked as a date table every single time in for Time Dimensions.   For and example of a Power BI Desktop DAX calculation not working when you are not using a marked date table,  check out this post.

Power BI Update Requests

Power BI updates every month and you may miss some of the things that the team released last month if you do not check out this site every month.  I know that the Power BI teams takes ideas for upcoming releases from Https://ideas.powerbi.com, and there is one thing that I really wish would get some more votes so that it can be added soon, which is another idea, like Marking Date Tables, which started out in SSAS Tabular. Having recently worked on a project with a ton of measures. The next feature that I hope that Power BI inherits from SSAS tabular is the ability to create folders for measures.   If you would like that too, vote for it here.

Yours Always,

Ginger Grant

Data aficionado et SQL Raconteur

What to use to Dynamically Write a Date Dimension in Power BI: M or DAX?

Calendar-clip-artRecently I needed to create a date dimension for a Power BI model as there was not one in the source database. There are two different ways that I could do this, using DAX from the Modeling Tab within the Data View or using M via the Query Editor window.  As a general rule, when it is possible data manipulation should be done in M as it offers a greater level of compression.  In this case though I am using a function in DAX, which is not the same as creating a calculated column.

Create a Date Table in DAX

To create a date table in DAX,  in Power BI go to the Data View->Modeling Table.  Click on the  New Table button on the ribbon.  For those who are wondering how you would go about writing either one, here is the source code for the DAX version.

 

DimDate = ADDCOLUMNS( CALENDAR(DATE(2017,1,1), DATE(2020,12,31)) ,
"Date Key", FORMAT ( [Date], "YYYYMMDD" ), //NumericDate
"Year", YEAR([Date]),
"Qtr Number", "Qtr " & FORMAT( [Date], "Q"),
"Q Number", "Q " & FORMAT( [Date], "Q"),
"Month Name" , FORMAT ( [Date], "mmmm" ) ,
"Month Short Name" , FORMAT ( [Date], "mmm" ) ,
"Month Number", MONTH([Date]),
"Month Year", FORMAT ( [Date], "mmm " ) & YEAR([Date]),
"Day Name", FORMAT ( [Date], "dddd" ), //Name for Each day of the week
"Day Short Name", FORMAT ( [Date],  "ddd" ),
"Day Number" , WEEKDAY ( [Date] ) //Sunday is 1
)  

This code uses the DAX CALENDAR function to create a contiguous set of dates between January 1, 2017 and December 31, 2020 with a field is named “Date”.  The remaining fields need field names and comments were added for clarity

Create a Date Table in M

In the Power BI Query Editor, click on the New Source button and select Blank Query. Select the View tab and click on the Advanced Editor button.  The Advanced Editor is where the M query is stored.  Paste the following code in to create a new table called Query1, which of course you can rename.

let
Source = List.Dates( #date(2017,1,1), Number.From( #date(2020, 12,31) - Date.From( #date(2017,1,1) ))+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"Column1", type date}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type",{{"Column1", "Date"}}),
#"AddYear" = Table.AddColumn(#"Renamed Columns", "Year", each Text.End(Text.From([#"Date"], "en-US"), 4), type text),
#"AddMonth" = Table.AddColumn(AddYear, "Month Number",   each Date.ToText([Date], "MM")),
#"AddQuarter" = Table.AddColumn(AddMonth, "Quarter Number", each "Qtr " & Number.ToText(Date.QuarterOfYear([Date]))),
#"AddMonthName" = Table.AddColumn(#"AddQuarter", "Month Name", each Date.ToText([Date], "MMMM")),
#"AddMonthShortName" = Table.AddColumn(#"AddMonthName", "Month Short Name", each Date.ToText([Date], "MMM")),
#"AddShortMonthYear" = Table.AddColumn(#"AddMonthShortName", "Short Month Year", each [Month Short Name] &" " &  [Year]),
#"AddDayOfWeek" = Table.AddColumn(#"AddShortMonthYear", "Day of Week", each Date.ToText([Date], "dddd")),
#"AddDay" = Table.AddColumn(#"AddDayOfWeek", "Day", each Date.ToText([Date], "dd")),
#"AddDateKey" = Table.AddColumn(AddDay, "DateKey", each ([Year]&[Month Number]&[Day]))
in
#"AddDateKey"

To Create A Date Table Use Either M or DAX

Now the question is which one should you use?  To be honest it doesn’t matter.  I couldn’t see any difference when I tested it.  To validate this answer I consulted twitter, which sparked some very interesting comments and analysis. Marco Russo b | t  is planning on writing a blog on the details of it, but Jason Thomas b | t gave me this summary. “[The] Dictionary expands as needed as values are inserted–designed to reduce cost of re-alloc and re-org of hash buckets (at cost of memory waste).” The full explanation needs a post of it’s own to be sure, which I am looking forward to reading when Marco writes it.   I’ll quote Kasper De Jonge b | t who summed it up best “I don’t believe it will matter much, the date table will be so small regardless”. Whichever way you chose to add a date table, DAX or M, which are needed for time intelligence, you now have the code to do either one.

 

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

 

DAX Date Calculations Not Working in Power BI Desktop? Here’s a fix.

Power BI offers a number of different ways to access data needed for data visualization and analysis. The reasons for selecting Power BI Desktop or Excel Power Pivot are more than likely going to change after I write this, but right now, Excel provides the ability to upgrade to a Tabular SSAS model, where Power BI Desktop does not. While Power BI Desktop and Excel both provide the ability to create formulas in DAX, only in Excel Power Pivot do you have the ability to use DAX Time and Date based calculations, but you can make Power BI Desktop do it, with help from Excel.

DAX Time and Date Calculations won’t work in Power BI Desktop

The DAX language has a number of Time and Date Calculations which contained common functions which relate to a given time frame. Here’s a list of Time and Date DAX Functions from Microsoft. These are very useful functions which you may find yourself wanting to do from time to time. None of them work in Power BI Desktop. Why? Power Pivot requires you to click on the Mark as Date Table to identify a table which contains column containing a list of unique Date and Time fields for DAX Date and Time functions to work. If you don’t have a table containing a list of dates and times in your model, you cannot use any DAX Time and Date Calculations. If you need a table like this, check out my blog post on Date table generation using a CTE to create one for you.  There is no place in Power BI Desktop, as of right now, to perform the equivalent of Mark as Date table which means that DAX Time and Date Calculations like SamePriorPeriodLastYear will not work.

Example of Power BI Desktop DAX Calculation Not Working

Here’s an example of something I tried to do in Power BI Desktop. In this first screen, I have created a new measure, which I called Prior Sales, which is designed to return the PriorPeriodnotWorkingprior period, which can be something smaller than year, of the Total Sales. The DAX for Total Sales is Total Sales = sum([Sales Amount]). Note there are no errors in the Prior Sales DAX measure.

 

Here’s what the report using the two measures, Total Sales and Prior Sales. Prior Sales shows no values but a total. This is exactly the same thing which happens in Excel when the Mark As Date Table has not been selected.

Fixing DAX Time Date Calculations in Power BI Desktop

What can you do to fix this problem? Fortunately the fix is pretty easy, but the only trick is you have to start by creating your date table. You cannot do it later, you will have to start over. Here are the steps. In Excel, create a Power Pivot Model and add in a ImportIntoExcelDate table. You do not have to have anything else in your model. Make sure that the date table is marked as a Date Table. Save the Excel Document and exit out of it. You are going to import this file into a Power BI Desktop Model. You do this by clicking on the menu button at the top Left of Power BI Desktop and selecting the menu option Import. If you do not see this option, you probably are not using the August 20, 2015 version of Power BI Desktop. Go get the latest version of Power BI from Microsoft which you can do here. Select Excel Workbook Contents, and you will get the Import window shown below. If you read the text, which to be honest I didn’t until after I hit Start PriorPeriodWorkingand ended up in a new Power BI file, you will see that the text clearly states a “A new Power BI file will be made for you”. This means when you import data into a Power BI Desktop file, you have to do it first, you can’t excel data to an  existing file. If you import the Excel file you just created with a date table marked as date table, and then add all of the other data you are interested in using, the DAX Date Time Functions will work.

Where should I model my Data, Excel Power Pivot or Power BI Desktop?

When I last wrote about where you should model your data, there was no work around to the DAX Time Date Calculation issue. That has changed with the August 20th release. If this is the only reason that you are choosing to model in Excel, I would think again. But if you want to migrate your model to a tabular analysis services solution at some point, you will need to model in Excel to be able to use the Visual Studio project which makes that process easy. I hope you found this helpful in deciding whether you should use Excel or Power Pivot. As Microsoft continues to release fixes, the reasons for choosing one tool or another change, and when they do, check back here as I am likely to blog about it.

Yours Always

Ginger Grant

Data aficionado et SQL Raconteur

***UPDATE: Power BI has been updated to include the ability to mark a table as date table, which means that you can now join on integer based date keys. For more information see this post.