{"id":86343,"date":"2020-02-14T14:43:51","date_gmt":"2020-02-14T14:43:51","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=86343"},"modified":"2022-04-24T21:39:29","modified_gmt":"2022-04-24T21:39:29","slug":"creating-time-intelligence-functions-in-dax","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/bi-sql-server\/creating-time-intelligence-functions-in-dax\/","title":{"rendered":"Creating Time-Intelligence Functions in DAX"},"content":{"rendered":"

The series so far:<\/strong><\/p>\n

    \n
  1. Creating Calculated Columns Using DAX<\/a><\/li>\n
  2. Creating Measures Using DAX<\/a><\/li>\n
  3. Using the DAX Calculate and Values Functions<\/a><\/li>\n
  4. Using the FILTER Function in DAX<\/a><\/li>\n
  5. Cracking DAX\u00a0\u2013 the EARLIER and RANKX Functions<\/a><\/li>\n
  6. Using Calendars and Dates in Power BI<\/a><\/li>\n
  7. Creating Time-Intelligence Functions in DAX<\/span><\/a><\/li>\n<\/ol>\n\n

    Want to compare this year\u2019s sales with the same period in the previous year? Chart year-to-date costs? Or perhaps you want to create a twelve-month moving average of profitability? In the last article in this series, you\u2019ll learn how to use the time-intelligence functions built into DAX and understand why they work.<\/p>\n

    Loading the Sample Data for this Article<\/h2>\n

    To work through the examples in this article, you\u2019ll need to download the worksheets from this workbook<\/a>. Tick the following worksheets to load data from this workbook into a new Power BI report:<\/p>\n

    <\/p>\n

    Now create a relationship between the Calendar<\/em> and Sales<\/em> tables by the SalesDate<\/em> column, as follows (note that initially the Balance<\/em> and Weight<\/em> tables aren\u2019t linked to any others):<\/p>\n

    <\/p>\n

    NOTE: This article assumes that you are only concerned with the date when sales are made. If you wanted to be able to choose between the sales date and the payment date when analysing data, you\u2019d either have to create multiple versions of the Calendar<\/em> table or multiple relationships, as described in the previous article<\/a> in this series.<\/p>\n

    Finally, switch to the Modeling ribbon and choose to sort the month name by the month number:<\/p>\n

    <\/p>\n

    And make the YearNumber<\/em> a text column:<\/p>\n

    <\/p>\n

    Again, the reason for both of these changes is covered in the previous article<\/a> in this series.<\/p>\n

    Creating a Draft Matrix<\/h2>\n

    To accommodate all the wonderful measures that you\u2019re going to bring into being, create a matrix visual to show total amount sold by year and month:<\/p>\n

    <\/p>\n

    I think it\u2019ll be easier to work with if you have all 12 months appearing, so choose to show months even when they have no corresponding data:<\/p>\n

    <\/p>\n

    Here\u2019s what the start of the matrix should look like:<\/p>\n

    <\/p>\n

    I\u2019ve decided to hide the row subtotals for the matrix. To turn these off, go to the formatting tab and look under the Subtotals section for Row subtotals.<\/p>\n

    <\/p>\n

    If for any reason you don\u2019t get the +\/- icons to expand\/collapse rows, enable this setting in the matrix\u2019s formatting properties:<\/p>\n

    <\/p>\n

    How Time-intelligence Functions Work<\/h2>\n

    To begin with, create a new measure on the Sales table to show total sales to date for each year (don\u2019t worry too much yet about how this works):<\/p>\n

    Year-to-date = CALCULATE(\r\n    SUM(Sales[Amount]),\r\n    DATESYTD('Calendar'[DateKey])\r\n)<\/pre>\n
    I\u2019ll come back to the DATESYTD<\/code> function in more detail later in this article, but for the moment I want to use it as an example to explain how any DAX time-intelligence function is calculated. Here\u2019s what this measure should show for the matrix:<\/p>\n
    <\/p>\n
    What I want to do is to focus on one figure \u2013 the year-to-date sales for March 2018. Start by looking at the sales amount for March 2018, which is 4.50:<\/p>\n
    <\/p>\n
    The filter context for this figure is all of the dates in March 2018:<\/p>\n
    <\/p>\n
    There\u2019s only one sale in March 2018, so that\u2019s why you get 4.50 as the sales for the cell:<\/p>\n
    <\/p>\n
    By contrast, the measure to calculate year-to-date sales first destroys the existing filter context for the sales date. Without any additional change, you would get the same figure for each month:<\/p>\n
    <\/p>\n
    However, the measure then replaces the filter context with one which picks out all of the dates from the calendar table which are on or before 31st<\/sup> March in 2018:<\/p>\n
    Year-to-date = CALCULATE(\r\n    SUM(Sales[Amount]),\r\n    DATESYTD('Calendar'[DateKey])\r\n)<\/pre>\n
    The filter context is now as follows for the cell:<\/p>\n
    <\/p>\n
    Power BI knows when days, months, quarters, and years start and end (they\u2019re called time-intelligence functions for a reason!). Curiously, Power BI doesn\u2019t know about weeks, so if you want to do weekly reporting, you\u2019ll have to create new aggregator columns yourself in your calendar table (again, the previous article<\/a> in this series gives a guide for how to do this sort of thing).<\/p>\n
    Here are the sales figures for the start of 2018:<\/p>\n
    <\/p>\n
    Adding 9.49 and 4.5 gives the year-to-date figure of 13.99! This is what every time-intelligence function does: it destroys the previous filter context for each value in a visual\u2019s underlying data and replaces it with a different one according to the combination of DAX functions you\u2019ve chosen.<\/p>\n
    Period to Date Functions<\/h2>\n
    Now to take a look in detail now at how to do specific things, beginning with calculating yearly, quarterly or monthly cumulative figures. You can do this using one of these functions:<\/p>\n