I would like to make a table in a report which counts whether a store has had 0 sales (blank/lack of data in this case) in a defined period of time.
The main problem I have is the dataset does not contain any information if a product in a store has not sold anything on that day. - Perhaps there is an easy way to populate my sold dataset with blank data if there are no sales?
Therefore I created a measure to check if on a certain day there were sales -> if not then fill that row with 0.
However, this requires each date/day to be included in the table for the measure to work as intended.
I would like the table to work as pictured; where there is a single line for a store which will count how many 0 sales days it has had and then the deliveries it has had in that time(can do that already). I also don't want to have the date column in the table if possible.
Let me know if there's anything else I can provide to help solve this problem
Thank you in advance
Solved! Go to Solution.
Add plus zero to the end of your formula.
Sales = SUM(Sold[Quantity Sold]) + 0
As a side note, i notice you are using Calculate but for no purpose. You can remove that from your formula.
Looks like that worked - could you please explain your solution a little so I know exactly what you did?
Is this measure the only thing you changed?
Yes. That is all i changed. The VALUES(DateKey[Date]) portion creates a unique list of all dates from the DateKey Table. Against every date, we then plot the sale and give a title to this column as ABCD. We thereafter filter the ABCD column on all values where the value is 0. Finally we count the days in the filtered Table.
If my reply helped, please mark it as Answer.