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
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?
Yes, that was very helpful and gave me what I needed.
Your solution relies on the modeling function within Power BI Desktop to create the Calendar table from data contained in the Data table. Is modeling like that possible using the Power BI Service? The ultimate goal is to use source data that's contained in OneDrive for Business and present the report on-line, either natively within powerbi.com or embedded in our SharePoint Online environment (in an Office 365 Group site). I can publish the .pbix file to the workspace, so I have a way of sharing the data, but I don't think I can take advantage of the auto-refresh that way. (I'm very new to Power BI, so maybe this is a dumb question.)