How to fill out data with missing dates

[Lauren_Michelle]

Hey everyone! 

I need help populating sales data with date gaps to avoid the non contiguous date range error.

My data set has 4 years of data and over 600 products. Whilst the data set has sales data for every single day when you look at it from an overall perspective, once you filter down to a particular product, if the product is new or has been removed from sale a while ago, all my tables and visuals 'break'. 

I have a sales dashboard where end users can see performance (YTD, MAT, Last week, L4W, L12W, L26W)  they can see these values and can also see % growth versus previous period and versus previous year.

Products are broekn down as following: Category (5 different categories), Sub Category (13 different sub categories), and Product name (+600 individual product names).

End users want to be able to see how individual products have performed YoY, most products have the full 4 years of history, however as certain products may no longer be available or other products have only recently started to be sold, their date range is non contiguous over the full 4 years. This means, even if I want to see performance of an individual product that has full history, the whole table breaks because of another non contiguous product in the same expanded drop down and the matrix stops displaying visuals altogether.

Currently I have limited expanding the table so end user can only see a top line view of category/sub category performance.

The only solution I can think of is to somehow populate the missing dates with a '0' value so that even if the product is no longer sold, or is new, it looks like it technically has 4 full years of contiguous data. However in terms of how to do this I am at a loss.

Desperate for help if anyone has some insight!

[Ghhousuddin · Accepted solution]

Hello!

It sounds like you need to create a calendar table in Power BI to ensure that you have a continuous date range for all your products. This will allow you to fill in the missing dates with '0' value as needed. Here's a step-by-step guide to achieve this:

Create a calendar table:
First, you need to create a separate calendar table that covers the entire date range of your data. You can create this table in Power BI using DAX.

a. Go to the 'Home' tab and click on 'Enter Data'.
b. Name the new table 'Calendar' and click 'Load'.
c. Select the 'Calendar' table and go to the 'Modeling' tab. Click on 'New Table'.
d. Enter the following DAX expression (adjust the date range accordingly):

DAX :

Calendar =
CALENDAR (DATE (2017, 1, 1), DATE (2021, 12, 31))

Create relationships between tables:
Now, you need to create a relationship between the calendar table and your sales data table.

a. Go to the 'Model' view and drag the 'Date' column from the Calendar table to the corresponding 'Date' column in your sales data table. This will create a relationship between the two tables.

Update your measures:
Modify your existing measures to use the new calendar table instead of the date column from the sales data table.

For example, if you have a measure like:

DAX : Total Sales = SUM (Sales[SalesAmount])

Update it to:

Total Sales =
CALCULATE (
SUM (Sales[SalesAmount]),
USERELATIONSHIP (Sales[Date], Calendar[Date])
)

Fill in missing dates with '0' value:
To fill in the missing dates with a '0' value, you can use the following measure:

DAX : Sales with Missing Dates =
IF (
ISBLANK ([Total Sales]),
0,
[Total Sales]
)

Now, you can use the 'Sales with Missing Dates' measure in your visuals and tables. This will ensure that even if a product has missing dates, it will display '0' value for those dates, and the visuals won't break when filtering by specific products.

Remember to update all your other measures to use the Calendar[Date] instead of Sales[Date], and your visuals should work as expected.

[Lauren_Michelle]

Thank you that's a really clear and detailed explanation!