Creating a data Timeline in Power BI

Imagine your boss sends you an urgent email that says that he needs a daily inventory levels report by Friday so that he can review it before a presentation to the board on Monday morning...and its Thursday afternoon.

Attached to his email is a spreadsheet that looks like this.

As you can see the product inventory levels are specified over time spans that range from the start date to the end date.

Also included is a screenshot of what he wants the report to look like.

The challenge, in order to produce this report you need the data spread out on a daily time line so that you have the daily inventory levels for every product. You do not have time to manually change the data in Excel and you cannot reach the boss to find out if he has a different dataset that has daily inventory levels.

The solution

If you had access to database with a date dimension table you could load the spreadsheet into a table in the database and do a Cartesian join between this new table and the date dimensions. Similar to the code below.

SELECT 
          p.*
        , d.date

  FROM 
          dbo.DimDate d
        , dbo.ProductInventory p
 WHERE 
          d.date <= NOW()
   AND 
          d.date BETWEEN p.[Start Date] AND p.[End Date] 

However for this sceneario you only have Power BI and not a lot of time so here is how you could do it using only Power BI.

Create date range in Power BI

First you will need to create a date range using M. This is required because you will use subsequent M queries to create the timeline dataset.

Here is the M code that generates a range of dates that cover all of the time ranges in the spreadsheet. From March 1, 2017 (2017 03 01) to May 1, 2017 (2017 05 01)

let
    Source = List.Dates(#date(2017, 03, 01), Number.From(#date(2017, 05, 01)) - Number.From(#date(2017, 03, 01)), #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"

Here is what the transformation steps look like in Power Query.

Note I deliberately added steps to rename and change the data type to make the M code easier to reverse engineer and understand. If you are good with M you can make this code far more terse.

Create a new Product Inventory Timeline dataset

You now need to create a new query that effectively does a Cartesian join between the Product Inventory Levels (spreadsheet data) and the generated date range (M query).

In this M code I bring the two data sets together and then filter out days that do not have any inventory values.

let
    source = #"Product Inventory Levels",
    #"Add GeneratedDateRange" = Table.AddColumn(source, "Dates", each #"GeneratedDateRange"),
    #"Expanded Dates" = Table.ExpandTableColumn(#"Add GeneratedDateRange", "Dates", {"Date"}, {"Dates.Date"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Dates",{{"Dates.Date", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "In Date Range Y/N", each if [Dates.Date] >= [Start Date]  and  [Dates.Date] <= [End Date]  then "Y" else "N" ),
    #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([#"In Date Range Y/N"] = "Y"))
in
    #"Filtered Rows"

In this code I have already loaded the spreadsheet data using a query called Product Inventory Levels. The first line of this M code references this query.

source = #"Product Inventory Levels",

Next I add a column that references the values from the generated date range query.

 #"Add GeneratedDateRange" = Table.AddColumn(source, "Dates", each #"GeneratedDateRange"),

Then I expand to the Date column in this new column table.

#"Expanded Dates" = Table.ExpandTableColumn(#"Add GeneratedDateRange", "Dates", {"Date"}, {"Dates.Date"}),

This creates a row for every product and for every day.

Next I add a Yes/No column to identify the rows that fall outside of any product level time frames.

 #"Added Conditional Column" = Table.AddColumn(#"Changed Type", "In Date Range Y/N", each if [Dates.Date] >= [Start Date] and [Dates.Date] <= [End Date] then "Y"else"N" ),

Last I filter out rows that do not have any product level values.

   #"Filtered Rows" = Table.SelectRows(#"Added Conditional Column", each ([#"In Date Range Y/N"] = "Y"))

Alternatively instead of filtering out these records I could have set the quantity to 0 for these days/records because we effectively do not have any inventory on these days.

Here is what the Power Query steps look like.

This gives you a new query and data that allows you to build a report that looks like this.

With this report you can see the daily fluctuation of product inventory levels over time at the day level.

If you would like to get a copy of the pbix file to further examine it you can download a copy from the following public repo.

Note I have embedded all the data required in the report by manually entering data for the product inventory levels spreadsheet into the report. Its easier to share this way.

Hopefully you have found this to be another practical post. Until next time.

Anthony