{"id":85983,"date":"2020-01-03T17:35:02","date_gmt":"2020-01-03T17:35:02","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=85983"},"modified":"2026-03-09T10:35:51","modified_gmt":"2026-03-09T10:35:51","slug":"using-calendars-and-dates-in-power-bi","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-calendars-and-dates-in-power-bi\/","title":{"rendered":"Power BI Calendar Table: Create &#038; Use Dates"},"content":{"rendered":"\n<p>A calendar table (also called a date table) is essential for time-based analysis in Power BI &#8211; without one, DAX time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR, and CLOSINGBALANCE won&#8217;t work. You can create a calendar table using the DAX CALENDARAUTO function (auto-detects date ranges) or the CALENDAR function (lets you specify exact start and end dates). This article explains why you need a calendar table, how to create one with custom fiscal years and non-standard periods, and how to handle multiple date columns in your data model.<\/p>\n\n\n<p><strong>The series so far:<\/strong><\/p>\n<ol>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/creating-calculated-columns-using-dax\/\">Creating Calculated Columns Using DAX<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/creating-measures-using-dax\/\">Creating Measures Using DAX<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/using-the-dax-calculate-and-values-functions\/\">Using the DAX Calculate and Values Functions<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/using-the-filter-function-in-dax\/\">Using the FILTER Function in DAX<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/cracking-dax-the-earlier-and-rankx-functions\/\">Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/using-calendars-and-dates-in-power-bi\/\">Using Calendars and Dates in Power BI<\/a><\/li>\n<li><a href=\"https:\/\/www.red-gate.com\/simple-talk\/sql\/bi\/creating-time-intelligence-functions-in-dax\/\"><span>Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n\n\n\n<p>Creating and using a calendar table is pretty straightforward, but this article will explain not just how to create a table, but also why you should want to do this. The article will also answer questions such as: what happens if you have two or more dates in the same table that you want to reference? Or if you have another table which holds information at a different level of granularity? Or if you want to report sales by bank holidays? Read on for how to create a robust data model for handling time-based data!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-load-the-sample-data-for-this-article\">How to load the sample data for this article<\/h2>\n\n\n\n<p>To work through the examples in this article, you\u2019ll need to download the worksheets from <a href=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/Simple-Talk-calendar-data.xlsx\">this workbook<\/a>. Tick all of the worksheets when you\u2019re loading data into a new Power BI report:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"304\" height=\"442\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image.png\" alt=\"\" class=\"wp-image-85984\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>These tables will give you the following data model:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"787\" height=\"279\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-1.png\" alt=\"\" class=\"wp-image-85985\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In addition, you should have the following four tables which aren\u2019t linked:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"579\" height=\"293\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-2.png\" alt=\"\" class=\"wp-image-85986\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You\u2019ll be using some of these tables in what follows, and some in the next article in the series.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-why-do-you-need-to-create-a-calendar-in-power-bi\">Why do you need to create a calendar in Power BI?<\/h2>\n\n\n\n<p>When I was first learning Power BI (actually, it was PowerPivot in those days, those many moons ago), I didn\u2019t initially see the point of calendars. After all, Power BI allows you to include fields from date hierarchies which are created automatically for you:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"267\" height=\"184\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-3.png\" alt=\"\" class=\"wp-image-85987\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>However, having a calendar table gives two big advantages:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>It allows you to aggregate data by non-standard columns (think your company\u2019s financial year, or your timesheet weeks, or the Mayan calendar!).<\/li>\n\n\n\n<li>It gives you access to all of the wonderful time-intelligence functions in DAX, with names like <code>TOTALYTD<\/code> and <code>CLOSINGBALANCE<\/code>. Without a calendar table these won\u2019t work.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>Given that most people\u2019s main interest in creating measures in DAX is to compare numbers across time periods, the second point is a bit of a clincher!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-what-are-the-requirements-for-a-calendar-in-power-bi\">What are the requirements for a calendar in Power BI?<\/h2>\n\n\n\n<p>What should a calendar table in Power BI look like? Here\u2019s an example:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"789\" height=\"197\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-4.png\" alt=\"\" class=\"wp-image-85988\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Thus, a calendar table should include one row for each date in your model in which you might be interested. In the example above, the table consists of all the dates in 2018, 2019 and 2020, since this is the lifespan of the transactions in the <strong>Sales<\/strong> worksheet. In addition, each date row should have a primary key (a unique field which tells you the date you\u2019re looking at). This doesn\u2019t have to be a date; you could use a separate numeric field instead. However, since dates are stored internally as numbers, I can\u2019t see any reason not to use a date column as your primary key, as above.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-a-calendar-in-dax\">How to create a calendar in DAX<\/h2>\n\n\n\n<p>DAX contains a couple of functions which will auto-generate a calendar table for you (this sounds like a good idea, but probably isn\u2019t \u2013 read on). One of these is the <code>CALENDARAUTO<\/code> function. To use this, click on the following tool found on the <em>Modeling<\/em> tab to create a new table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"324\" height=\"128\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-5.png\" alt=\"\" class=\"wp-image-85989\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Type in a name for your table. Here I\u2019ve called mine <em>My calendar<\/em>. Then use the <code>CALENDARAUTO<\/code> function to say what it will contain:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"614\" height=\"91\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-6.png\" alt=\"\" class=\"wp-image-85990\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In this case, just assume that the fiscal year ends in December. You could leave out the argument 12, since December \u2013 month 12 \u2013 is the default anyway:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My calendar = CALENDARAUTO(12)<\/pre>\n\n\n\n<p>When you confirm, you\u2019ll see that this formula Power BI will create a set of dates going from the first date it finds in your data model to the last. In this example the first date is 10<sup>th<\/sup> February 2018 and the last date is 8<sup>th<\/sup> January 2020. Because the financial year ends in December, the function will generate a table containing all the dates for the months January through to December for the years 2018, 2019 and 2020:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"439\" height=\"331\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-7.png\" alt=\"\" class=\"wp-image-85991\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This was all very quick, but also not that useful, as you\u2019re now going to have to add columns giving the year, month, quarter and so on for each date AND then do the same thing for each model that you create.<\/p>\n\n\n\n<p>A variation of the above is the <code>CALENDAR<\/code> function, which lets you specify a start and end date:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"421\" height=\"84\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-8.png\" alt=\"\" class=\"wp-image-85992\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This works in exactly the same way (and suffers from the same drawbacks), but it does at least give you more control over which dates are generated.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-a-calendar-in-excel\">How to create a calendar in Excel<\/h2>\n\n\n\n<p>Another way to create a calendar is using an Excel spreadsheet. To do this, type in a column heading and the first couple of dates, and click and drag down using the black cross shown:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"201\" height=\"172\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-9.png\" alt=\"\" class=\"wp-image-85993\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You can now add columns giving the year number, month number, etc. For example, for the year number:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"263\" height=\"149\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-10.png\" alt=\"\" class=\"wp-image-85994\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You could then double-click on this black cross to copy this formula down:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"270\" height=\"168\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-11.png\" alt=\"\" class=\"wp-image-85995\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Here are some functions that you could use:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li><code>=YEAR(A2)<\/code> \u2013 to get the year number, as above<\/li>\n\n\n\n<li><code>=MONTH(A2)<\/code> \u2013 to get the month number, as above<\/li>\n\n\n\n<li><code>=TEXT(A2,\"mmmm\")<\/code> \u2013 to get the month name<\/li>\n\n\n\n<li><code>=TEXT(A2,\"mm - mmmm\")<\/code> \u2013 to get the month number\/name<\/li>\n\n\n\n<li><code>=DAY(A2)<\/code> &#8211; to get the day number<\/li>\n\n\n\n<li><code>=TEXT(A2,\"dddd\")<\/code> \u2013 to get the day name<\/li>\n\n\n\n<li><code>=\"Q\" &amp; INT((MONTH(A2)+2)\/3)<\/code> \u2013 to get the quarter number<\/li>\n<\/ul>\n<\/div>\n\n\n<p>You could then save the Excel workbook (possibly pasting the formulae as values first) and use this as a source for your Power BI calendar table.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-a-calendar-in-sql-server\">How to create a calendar in SQL Server<\/h2>\n\n\n\n<p>If you\u2019re using SQL Server, this is probably the best option. Here\u2019s a sample procedure which you can adapt to generate one row for every date in a given range. It doesn\u2019t claim to be that efficient (it uses cursors!), but you\u2019re only going to have to run it once.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">CREATE PROC spCreateCalendarTable (\n\t@StartDate datetime = '20180101',\n\t@EndDate datetime = '20201231'\n) AS\n-- create a table of dates for use in Power BI\n-- first get rid of any old versions of table\nDROP TABLE IF EXISTS tblCalendar\n-- create the table of dates\nCREATE TABLE tblCalendar(\n\tDateKey date PRIMARY KEY,\n\tYearNumber int,\n\tMonthNumber int,\n\t[MonthName] varchar(10),\n\tDayNumber int,\n\t[DayName] varchar(10),\n\t[Quarter] char(2)\n)\n-- now add one date at a time\nDECLARE @i int = 0\nDECLARE @curdate datetime = @StartDate\nWHILE @curdate &lt;= @EndDate\n\tBEGIN\n\t-- add a record for this date \n        --(could use FORMAT function if SQL Server 2012 or later)\n\tINSERT INTO tblCalendar (\n\t\tDateKey,\n\t\tYearNumber,\n\t\tMonthNumber,\n\t\t[MonthName],\n\t\tDayNumber,\n\t\t[DayName],\n\t\t[Quarter]\n\t) VALUES (\n\t\t@curdate,\n\t\tYear(@curdate),\n\t\tMonth(@curdate),\n\t\tDateName(m,@curdate),\n\t\tDay(@curdate),\n\t\tDateName(weekday,@curdate),\n\t\t-- the quarter number\n\t\t'Q' + CAST(floor((month(@curdate)+2)\/3) AS char(1))\n\t)\n\t-- increase iteration count and current date\n\tSET @i += 1\n\tSET @curdate = DateAdd(day,1,@curdate)\nEND<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-use-a-calendar\">How to use a calendar <\/h2>\n\n\n\n<p>Once you\u2019ve created a calendar, here\u2019s how to use it. First load it into your data model (in this case, it was loaded with the Excel data), then link it to a date column. Here I\u2019ve assumed that you want to analyse sales by the sale date and not the payment date. Later in this article you\u2019ll learn how to cope with the situation where you have two or more dates in a table.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"424\" height=\"280\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-12.png\" alt=\"\" class=\"wp-image-85996\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You now need to tell Power BI that the <em>Calendar<\/em> table is &#8230; a calendar table! To do this, make sure you\u2019re looking at the calendar table in <em>Data<\/em> view:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"863\" height=\"194\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-13.png\" alt=\"\" class=\"wp-image-85997\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>On the <em>Modeling<\/em> tab, choose to mark this as a calendar table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"324\" height=\"176\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-14.png\" alt=\"\" class=\"wp-image-85998\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Choose the column which uniquely identifies each date, then choose <em>OK<\/em>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"709\" height=\"425\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-15.png\" alt=\"\" class=\"wp-image-85999\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>The only problem with all of this is that I\u2019m not convinced it\u2019s necessary! It certainly can\u2019t do any harm, but my understanding is that if you\u2019ve chosen a date column as your primary key, DAX time-intelligence functions will work even if you omit this step.<\/p>\n\n\n\n<p><strong>Read also:<\/strong><br><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/using-the-dax-calculate-and-values-functions\/\" target=\"_blank\" rel=\"noreferrer noopener\">How to use the DAX CALCULATE function<br><\/a><a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/using-a-subquery-in-a-select-statement\/\" target=\"_blank\" rel=\"noreferrer noopener\">Using subqueries in SQL for data preparation<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-fine-tune-your-power-bi-calendar-table\">How to fine-tune your Power BI calendar table<\/h2>\n\n\n\n<p>To see the <em>Calendar<\/em> table in action, create a matrix based upon your calendar, using these fields:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"231\" height=\"306\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-16.png\" alt=\"\" class=\"wp-image-86000\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You\u2019ll get something like this:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"276\" height=\"297\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-17.png\" alt=\"\" class=\"wp-image-86001\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>There are two problems here: Power BI has assumed that the year number is an integer which needs summing, and it has also assumed that the month name is text which can be sorted alphabetically. There are other solutions to both problems, but the simplest ones are as follows. First, change the year number to a text column:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"627\" height=\"286\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-18.png\" alt=\"\" class=\"wp-image-86002\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Secondly, with the month name column selected, choose to sort it by month number:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"741\" height=\"302\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-19.png\" alt=\"\" class=\"wp-image-86003\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You can now create a matrix with these fields:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"228\" height=\"307\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-20.png\" alt=\"\" class=\"wp-image-86004\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>To get this visual:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"166\" height=\"199\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-21.png\" alt=\"\" class=\"wp-image-86005\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Note that instead of the visual shown above, you may get something like this one:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"151\" height=\"91\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-22.png\" alt=\"\" class=\"wp-image-86006\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>In this case, try finding and setting the <em>+\/-icons<\/em> settings for your matrix in the <em>Row headers<\/em> card, to enable you to expand and collapse rows:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"259\" height=\"1087\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-23.png\" alt=\"\" class=\"wp-image-86007\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>If you don\u2019t have this property, it may be that you\u2019re using an older version of Power BI, in which case, drill down to show all the levels of detail for your matrix:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"485\" height=\"214\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-24.png\" alt=\"\" class=\"wp-image-86008\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>As one final touch, it would be nice to have all of the months appearing, so choose to show items with no date by clicking on the drop arrow next to the <em>MonthName<\/em> column:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"429\" height=\"384\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-25.png\" alt=\"\" class=\"wp-image-86009\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>And finally, you\u2019ll see the perfect matrix!<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"310\" height=\"328\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-26.png\" alt=\"\" class=\"wp-image-86010\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-dealing-with-different-levels-of-granularity\">Dealing with different levels of granularity<\/h2>\n\n\n\n<p>Suppose now that you want to compare actual and forecast sales which is a common enough requirement. This should be easy \u2013 you already have a table of monthly forecasts for sales:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"381\" height=\"443\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-27.png\" alt=\"\" class=\"wp-image-86011\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>However, these forecasts are by month and the <em>Calendar<\/em> table is by date. The easiest solution is to create another column which arbitrarily assigns each forecast to the first day of the month in which it occurs:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"574\" height=\"195\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-28.png\" alt=\"\" class=\"wp-image-86012\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Here\u2019s the formula used:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">ForecastDate = DATE([ForecastYear],[ForecastMonth],1)<\/pre>\n\n\n\n<p>It\u2019s probably a good idea to narrow the data type for this column from <em>Date\/Time<\/em> to just <em>Date<\/em>:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"185\" height=\"195\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-29.png\" alt=\"\" class=\"wp-image-86013\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You can now create a relationship between this new forecast date column and the calendar\u2019s date key column:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"626\" height=\"286\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-30.png\" alt=\"\" class=\"wp-image-86014\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This will enable you to compare actual and forecast data at any level of granularity down to month:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"247\" height=\"320\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-31.png\" alt=\"\" class=\"wp-image-86015\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Note that you\u2019ll obviously have to be careful not to drill down to day level, since the forecast sales have been arbitrarily assigned to the first day in each calendar month and the results would be misleading.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-create-new-aggregator-columns-like-public-holidays\">How to create new aggregator columns (like public holidays)<\/h2>\n\n\n\n<p>Readers outside the UK will need to know that a bank (public) holiday is a day which is treated like a Saturday or Sunday (that is, you don\u2019t have to go to work); there are about 10 of them each year, including Christmas Day, Boxing Day, New Year\u2019s Day, etc. The solution divides sales into working and non-working days where a non-working day is either a Saturday, a Sunday or a bank holiday. To do this, create some new columns in the <em>Calendar<\/em> table.<\/p>\n\n\n\n<p>Note that you can use this principle to report by any type of date: examples could include periods when you\u2019re offering a discount to customers, timesheet weeks, times of the day when shops are open, etc.<\/p>\n\n\n\n<p>Although you could just create a complicated single column, to make things easier to understand \u2013 and to work with \u2013 you\u2019ll create three:<\/p>\n\n\n<div class=\"block-core-list\">\n<ol class=\"wp-block-list\">\n<li>A column saying whether this is a weekend or not.<\/li>\n\n\n\n<li>A column saying whether this is a bank holiday or not.<\/li>\n\n\n\n<li>A column combining these two conditions.<\/li>\n<\/ol>\n<\/div>\n\n\n<p>To do the first, use the <code>WEEKDAY<\/code> function, but tweak the second argument so that it returns 6 for Saturday or 7 for Sunday:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"682\" height=\"109\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-32.png\" alt=\"\" class=\"wp-image-86016\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>So the full calculated column will be:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">If weekend = IF(WEEKDAY([DateKey],2)&gt;5,TRUE(),FALSE())<\/pre>\n\n\n\n<p>You could use the shorter form, if you\u2019re comfortable with Boolean algebra!<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">If weekend = (WEEKDAY([DateKey],2)&gt;5)<\/pre>\n\n\n\n<p>This shows, for example, that Christmas Day 2019 was on a Wednesday (that is, 3 days before the next weekend started):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"630\" height=\"343\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-33.png\" alt=\"\" class=\"wp-image-86017\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>To say whether a day is a bank holiday, for example, you should first create and load a table of bank holidays (or use the one supplied in this article\u2019s Excel workbook):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"459\" height=\"283\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-34.png\" alt=\"\" class=\"wp-image-86018\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now create a one-to-one relationship between the two tables:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"496\" height=\"207\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-35.png\" alt=\"\" class=\"wp-image-86019\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>It\u2019s created as one-to-one automatically because the <code>DateKey<\/code> is unique in the <em>Calendar<\/em> table, but the <code>BankHolidayDate<\/code> column is also unique in the bank holidays table too.<\/p>\n\n\n\n<p>You can now create another calculated column in the <em>Calendar<\/em> table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"439\" height=\"300\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-36.png\" alt=\"\" class=\"wp-image-86020\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Here\u2019s the code used, for copying:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">If bank holiday = IF(\n    \n    \/\/ if for this date there's no corresponding row\n    \/\/ in the bank holidays table ...\n    ISBLANK(RELATED(BankHoliday[BankHolidayDate])),\n    \/\/ ... then it ISN'T a bank holiday\n    FALSE(),\n    \/\/ otherwise it is\n    TRUE()\n)<\/pre>\n\n\n\n<p>This shows that Christmas Day 2019 was a bank holiday as expected:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"741\" height=\"329\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-37.png\" alt=\"\" class=\"wp-image-86021\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You could now combine the two conditions to get the status of any day:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"864\" height=\"347\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-38.png\" alt=\"\" class=\"wp-image-86022\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>This method will allow you to create reports dividing sales into working and not-working days, although the results aren\u2019t that exciting because as it happens no sales were made on a bank holiday:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"676\" height=\"169\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-39.png\" alt=\"\" class=\"wp-image-86023\"\/><\/figure>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/how-to-filter-dax-for-paginated-reports\/\" target=\"_blank\" rel=\"noreferrer noopener\">Filtering DAX for paginated reports<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-how-to-handle-multiple-dates-in-a-power-bi-calendar-table\">How to handle multiple dates in a Power BI calendar table<\/h2>\n\n\n\n<p>How do you cope when (as is nearly always the case in the real world) a table has two or more dates? For this example, how could you create a visual comparing the month of purchase with the month of payment? There are two ways. One way is to have multiple relationships between the same tables and specify in your measures which one you want to reference:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"456\" height=\"295\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-40.png\" alt=\"\" class=\"wp-image-86024\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>The second method is to use multiple versions of the calendar table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"691\" height=\"288\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-41.png\" alt=\"\" class=\"wp-image-86025\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Which solution you prefer will tell you a bit about what sort of person you are \u2013 think of it as a simple personality test. If you\u2019re the sort of person who likes technology for its own sake, you\u2019ll probably prefer the first solution: you\u2019ll like the fact that you\u2019re not storing the calendar table more than once, and you\u2019ll be prepared to sacrifice a bit of ease of use. If on the other hand, you\u2019re the sort of person who likes technology solely as a means to an end, you\u2019ll probably prefer the second solution. Even though it involves holding multiple copies of the calendar table, the resulting model is easier to work with.<\/p>\n\n\n\n<div class=\"note\">\n<p><em>The Wise Owl Recommendation<\/em><\/p>\n<p>If you\u2019re interested, I prefer the second method (but then I do work for a <a href=\"https:\/\/www.wiseowl.co.uk\/power-bi\/\">training company<\/a>, so by temperament am likely to want to make things as easy to use as possible). However, it doesn\u2019t make you a bad person if you prefer the first method \u2013 just different to me.<\/p>\n<\/div>\n\n\n\n<p>Both methods are shown under separate headings below. I\u2019ll begin with the multiple table approach since it\u2019s easier to understand and is probably the one that most people will use.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-multiple-tables-for-multiple-dates-in-a-power-bi-calendar\">Multiple tables for multiple dates in a Power BI calendar<\/h2>\n\n\n\n<p>For this method, start by renaming the first calendar table that you\u2019ve imported. For the model below, the calendar table is linked to the <em>SalesDate<\/em> column (the date on which a purchase took place), so I\u2019ve renamed the <em>Calendar<\/em> table to <em>PurchaseCalendar<\/em> to make it clear what\u2019s going on. I\u2019ve also removed some of the calendar columns to keep the table simple:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"446\" height=\"287\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-42.png\" alt=\"\" class=\"wp-image-86026\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>If you have the energy, it would probably be a good idea to rename each of the columns in this table too:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"463\" height=\"276\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-43.png\" alt=\"\" class=\"wp-image-86027\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Now choose to load another version of the calendar table using your recent sources (the workbook or database from which you loaded the calendar table will be listed here):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"555\" height=\"208\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-44.png\" alt=\"\" class=\"wp-image-86028\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Choose to import another version of the calendar table:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"291\" height=\"145\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-45.png\" alt=\"\" class=\"wp-image-86029\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Drag this onto the same layout diagram, and create a relationship between the <em>SalesData<\/em> table and your recently loaded calendar table, but this time using the <em>PaymentDate<\/em> column as the link field:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"729\" height=\"285\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-46.png\" alt=\"\" class=\"wp-image-86030\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Once again, you could now rename this version of the calendar table (and also rename the columns it contains) to make it clearer what\u2019s what. In this example, I\u2019ve also deleted some columns I don\u2019t want:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"727\" height=\"284\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-47.png\" alt=\"\" class=\"wp-image-86031\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>There are two arguments you could make against this approach: that it wastes memory, and that it clutters up your model. Both are true. But it doesn\u2019t waste that much memory. Each table stores about 1,000 dates, which is peanuts in today\u2019s memory terms. It doesn\u2019t have to clutter up your model if you use different tabs like this (one tab for each table containing multiple dates):<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"418\" height=\"84\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-48.png\" alt=\"\" class=\"wp-image-86032\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Having loaded all of your calendar tables and created the necessary relationships, you could use your model to create a matrix like this, showing the lag between purchases made and payments received:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"403\" height=\"109\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-49.png\" alt=\"\" class=\"wp-image-86033\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Here\u2019s what the fields for this visual look like:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"225\" height=\"289\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-50.png\" alt=\"\" class=\"wp-image-86034\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>Although the extra table is a bit messy, it does mean that you don\u2019t have to create any additional measures: you can just drag fields into the field well as usual.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-multiple-relationships-for-multiple-dates-in-a-power-bi-calendar\">Multiple relationships for multiple dates in a Power BI calendar<\/h2>\n\n\n\n<p>The alternative approach is to load one version of the calendar table, but create a second relationship:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"467\" height=\"288\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-51.png\" alt=\"\" class=\"wp-image-86035\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>There are now two relationships:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>The active relationship (the one with the solid line) is between the <em>DateKey<\/em> column in the <em>Calendar<\/em> table and the <em>SalesDate<\/em> column in the <em>Sales<\/em> table<\/li>\n\n\n\n<li>The selected relationship above (the one with the dotted line) is between the <em>DateKey<\/em> column in the <em>Calendar<\/em> table and the <em>PaymentDate<\/em> column in the <em>Sales<\/em> table<\/li>\n<\/ul>\n<\/div>\n\n\n<p>You can change which is the active relationship by right-clicking on it and choosing to show its properties:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"456\" height=\"254\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-52.png\" alt=\"\" class=\"wp-image-86036\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You can then tick the box to make a relationship the active one, but only after making all of the other relationships inactive first, as the following screenshot explains:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"830\" height=\"302\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-53.png\" alt=\"\" class=\"wp-image-86037\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>If you\u2019re going to use multiple relationships, it may be a good idea to make all of them inactive. Then you won\u2019t inadvertently create a measure referring to the wrong relationship by mistake:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"479\" height=\"281\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-54.png\" alt=\"\" class=\"wp-image-86038\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>What you now have to do is to create measures saying for any calculation to which table it should refer. For example, suppose you want to show for each year:<\/p>\n\n\n<div class=\"block-core-list\">\n<ul class=\"wp-block-list\">\n<li>The total sales made in that year; and<\/li>\n\n\n\n<li>The total sales paid for in that year.<\/li>\n<\/ul>\n<\/div>\n\n\n<p>To do this, create two measures using the <code>USERELATIONSHIP<\/code> function in each case. Here\u2019s the first one:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sales by date made = CALCULATE(\n    SUM(Sales[Amount]),\n    USERELATIONSHIP(\n        'Calendar'[DateKey],\n        Sales[SalesDate]\n    )\n)<\/pre>\n\n\n\n<p>And here\u2019s the second:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Sales by date paid = CALCULATE(\n    SUM(Sales[Amount]),\n    USERELATIONSHIP(\n        'Calendar'[DateKey],\n        Sales[PaymentDate]\n    )\n)<\/pre>\n\n\n\n<p>These measures will allow you to show the required figures:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"418\" height=\"173\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-55.png\" alt=\"\" class=\"wp-image-86039\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>However, there\u2019s no way that I can see to display an aged debtor matrix like the one created for the multiple table approach. It\u2019s also a bit irritating that in the <code>USERELATIONSHIP<\/code> function that you have to specify the two columns that you\u2019re joining together. It would be better if you could just specify which relationship you\u2019re using. Something like this in fact:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"504\" height=\"404\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-56.png\" alt=\"\" class=\"wp-image-86040\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>You might also expect that because you are referencing the start column and end column of the relationship, you don\u2019t need the relationship to actually exist, but you\u2019d be wrong, as this error message which appears if you delete the above relationships shows:<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"531\" height=\"270\" src=\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2020\/01\/word-image-57.png\" alt=\"\" class=\"wp-image-86041\"\/><\/figure>\n\n\n\n<p><\/p>\n\n\n\n<p>And with that mild bit of whingeing, that\u2019s the end of this article!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-conclusion\">Conclusion<\/h2>\n\n\n\n<p>In this article, you\u2019ve learned how and why you might want to create a calendar table in Power BI, how to use it to report on figures at different levels of granularity, how to add additional aggregator columns to the table and two different ways to cope with the situation where you have more than one date column in the same table. In the next and final article of this series, I\u2019ll show how to use the calendar(s) that you\u2019ve created to show things like year-to-date figures, cross-period comparisons and moving averages.<\/p>\n\n\n\n<p><strong>Read also:<\/strong> <a href=\"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/database-administration-sql-server\/automated-script-generation-with-powershell-and-smo\/\" target=\"_blank\" rel=\"noreferrer noopener\">Automating report generation with PowerShell<\/a><\/p>\n\n\n\n<section id=\"faq\" class=\"faq-block my-5xl\">\n    <h2>FAQs: Using Calendars and Dates in PowerBI<\/h2>\n\n                        <h3 class=\"mt-4xl\">1. Why do you need a calendar table in Power BI?<\/h3>\n            <div class=\"faq-answer\">\n                <p>A calendar table gives you two things: the ability to aggregate data by custom time periods (fiscal years, ISO weeks, custom accounting periods) and access to DAX time intelligence functions like TOTALYTD, SAMEPERIODLASTYEAR, and DATEADD. Without a marked-as-date-table calendar, these functions simply won&#8217;t work in your measures.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">2. How do you create a calendar table in DAX?<\/h3>\n            <div class=\"faq-answer\">\n                <p>Use CALENDARAUTO() to auto-generate a date range from the earliest to latest date in your model, or use CALENDAR(DATE(2020,1,1), DATE(2025,12,31)) to specify exact dates. Then add calculated columns for Year, Month, Quarter, Week Number, and any custom periods your business needs. Mark the table as a Date Table in Power BI&#8217;s modeling tab.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">3. How do you handle multiple dates in Power BI?<\/h3>\n            <div class=\"faq-answer\">\n                <p>If your fact table has multiple date columns (e.g., Order Date and Ship Date), create a single shared calendar table and use role-playing dimensions. In the Power BI model, create one active relationship to your primary date and use USERELATIONSHIP in CALCULATE to activate the other date relationships when needed.<\/p>\n            <\/div>\n                    <h3 class=\"mt-4xl\">4. What is the difference between CALENDARAUTO and CALENDAR in DAX?<\/h3>\n            <div class=\"faq-answer\">\n                <p>CALENDARAUTO scans your entire data model and generates dates from the first to last date it finds, aligned to your fiscal year end month. CALENDAR lets you specify the exact start and end dates. CALENDAR is preferred for production models because it gives you explicit control and doesn&#8217;t change when your data changes.<\/p>\n            <\/div>\n            <\/section>\n","protected":false},"excerpt":{"rendered":"<p>Learn how to create calendar tables in Power BI using DAX functions like CALENDARAUTO and CALENDAR. Covers fiscal years, multiple date relationships, and time intelligence setup.&hellip;<\/p>\n","protected":false},"author":9783,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143528],"tags":[95509],"coauthors":[6782],"class_list":["post-85983","post","type-post","status-publish","format-standard","hentry","category-bi-sql-server","tag-standardize"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/9783"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=85983"}],"version-history":[{"count":11,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85983\/revisions"}],"predecessor-version":[{"id":109022,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/85983\/revisions\/109022"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=85983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=85983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=85983"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=85983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}