Forum Discussion
missing data
Hi All
I have sales data per customer which I would like to display in line chart for whole year comparing YOY sale.
Majority of the customers do not place orders every day or sometimes for the whole week.
Is there a way to add, for the missing weeks, zero value so that the line chart will be one uninterrupted line
below my sales data sample
thank you
Anonymous,
You could create a date table in your data model, and then cretae ralationship between your original table and this date table. Here is the sample DAX expression to create a date table.
Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))Please refer to the link below to see the details.
https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/Regards,
Charlie Liao
7 Replies
Anonymous,
You could create a date table in your data model, and then cretae ralationship between your original table and this date table. Here is the sample DAX expression to create a date table.
Date =CALENDAR (DATE(2000;1;1); DATE(2025;12;31))Please refer to the link below to see the details.
https://kohera.be/blog/business-intelligence/how-to-create-a-date-table-in-power-bi-in-2-simple-steps/Regards,
Charlie Liao
I have the same kind of issue as the original poster. I have many months of customer purchase data, which I have aggregated by quarter (2016-1, 2016-2, 2016-3, etc.). I want to create a line chart visualization that displays every quarter between 2015-1 and 2017-4, including quarters where the total purchases were zero. But I don't have any records in my database when there were no purchases.
I can easily create a table that contains a record for every quarter in the time period, but how would I create a relationship between that table and my purchase records table that would force a zero value to display for a given quarter when there are no matching purchase records? None of the join options seem to do this.
