Forum Discussion
missing data
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
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.
This is highly oversimplified, but imagine the data is something like this:
Date InvoiceAmount Quarter
15Jan2016 500 2016-01
15Feb2016 250 2016-01
31Aug2016 1000 2016-03
30Nov2016 1500 2016-04
To get the value for quarter, I have extracted the year and quarter values from the Date column, transformed them into text, and then concatenated them using the Merge Columns feature.
When I aggregate by quarter, I have sales of 750 in 2016-01, 1000 in 2016-03, and 1500 in 2016-04. If I graph that, sorted by quarter, it will look like a steadily climbing line from left to right. But note that there are zero sales in Q2. I want my graph to dip from 750 to zero for Q2 and then go back up again for Q3. How do I tell Power BI to display a value for 2016-02 even though there are no data points to aggregate for that time period?
