Count Blanks Table

[WillEyedowin]

Hi All,

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.

Current Table - need each date in row for measure to work properly

Measure to determine blank data on that day = 0 sales

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.

Desired table to look like thisRelationship TableThe sold table - showing when a product in a store sells(no data if no sale on that day)

Let me know if there's anything else I can provide to help solve this problem

Thank you in advance 🙂

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[WillEyedowin]

Hi, Try this:

https://drive.google.com/drive/folders/1fHlIKulfA5Brl5HQuSQ97w_gAynlkfvi?usp=sharing

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.

Hope this helps.

[WillEyedowin]

Hi,

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?

Zero Sales Store = COUNTROWS(FILTER(SUMMARIZE(VALUES(DateKey[Date]),[Date],"ABCD",SUM(sold[Quantity Sold])),[ABCD]=0))

Thanks

[Ashish_Mathur]

Hi,

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.

[Anonymous]

Add plus zero to the end of your formula.

i.e.

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.