cancel
Showing results for
Did you mean:
Frequent Visitor

## Count Blanks Table

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

Hi,

Hope this helps.

6 REPLIES 6
Super User

## Re: Count Blanks Table

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.

Proud to be a Datanaut!

Super User

Hi,

Frequent Visitor

Highlighted
Super User

Hi,

Hope this helps.

Frequent Visitor

## Re: Count Blanks Table

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
Super User

## Re: Count Blanks Table

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.