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
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.)
You are welcome. I do not use the PowerBI service very much. Nevertheless, i do not think you can write any formulas there - you can only create visuals there.
If my reply helped, please mark it as Answer.