Forum Discussion
Generating data when there is no sale
Hello everyone,
I'm working with a model that contains information about sales of different products in different centers. The fact table of sales contains data only for the days that a product has been sold in a center, which means that I have no info if a product has not been sold in a day. I need to "physically" have data for every day that this happens, so instead of not having a row, I would have a row containing the information of that day, the center, the product and quantity 0.
I know I can use "Show items with no data" in the visual object, but that's not what I'm looking for since I need to have the rows in the table.
An example of what I'm saying is this:
Current info:
| Date (dd/mm/yyyy) | Center | Product | Qty |
| 01/01/2020 | A | P1 | 15 |
| 01/01/2020 | A | P2 | 7 |
| 02/01/2020 | A | P1 | 9 |
| 03/01/2020 | A | P1 | 10 |
| 03/01/2020 | A | P2 | 6 |
Desired info:
| Date (dd/mm/yyyy) | Center | Product | Qty |
| 01/01/2020 | A | P1 | 15 |
| 01/01/2020 | A | P2 | 7 |
| 02/01/2020 | A | P1 | 9 |
| 02/01/2020 | A | P2 | 0 |
| 03/01/2020 | A | P1 | 10 |
| 03/01/2020 | A | P2 | 6 |
As you can see, I added info with Qty 0 for the Center "A", Product "P2" on day "02/01/2020". This is what I'm trying to automatically add.
Do you guys know how can I make this in PBI?
Thank you in advance.
This can be done in the query editor as follows.
Create a list of dates with List.Dates spanning the range you need and convert it to a table
Add a column with a table of your Product and Centers, and expand it
At this point you'll have a row for every combinations of Date and Product/Center
Merge your actual data table into this new table on Date and Product columns
Expand the merged table
Replace nulls (rows that didn't have a match) with zeros
Regards,
Pat
11 Replies
Hi Anonymous,
Please take a look on this article: https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-show-items-no-data
And I think once you have a row with data with 'Blank' Product Qty, you can use the following formula:
IF (CALCULATE ( SUM ( Product Qty) ) = BLANK (),0, CALCULATE ( SUM ( Product Qty) ))
This will return 0 when you have a Blank.
Please let me know if this works.
Best,
Ema- amitchandak
Super User
Anonymous , You can add +0 to the measure
like sum(Table[Qty]) +0
or use this option - Show Item with No data
Hello amitchandak ,
Thank you for your reply. As I said, I know I can use the "show items with no data" option, but it's not what I'm looking for. I'm actually trying to generate the rows in the dataset, so I have physical rows for every day that has no sale.
PaulDBrownCommunity Champion
Anonymous
Both the methods suggested by Anonymous @ and amitchandak work, but you need to use a date table with continuous dates covering the range of dates in your fact table (not the date from your fact table) in your visual.
Here is the model as I have it, and highlighted in red are the dimension tables I've used to create the table visual. The measures are according to Anonymous and amitchandak suggestions:
- mahoneypat
Microsoft Employee
This can be done in the query editor as follows.
Create a list of dates with List.Dates spanning the range you need and convert it to a table
Add a column with a table of your Product and Centers, and expand it
At this point you'll have a row for every combinations of Date and Product/Center
Merge your actual data table into this new table on Date and Product columns
Expand the merged table
Replace nulls (rows that didn't have a match) with zeros
Regards,
Pat
