Forum Discussion
Counting Records that don't exist
Been trying to figure out a good way to Count records that has no value.
In this measure i'm trying to count the numbers of Stores by Product and week that have no sales. I got it semi working but because im on a Calendar, some dates go into other weeks which results in a wrong number in certain weeks between months which I circle in blue. Is there a better way to go about this?
Void =
COUNTROWS(FILTER(
SUMMARIZECOLUMNS(
'Date Table'[Calendar Year],
'Date Table'[Calendar Week],
DimItem[Product],
DimStore[Store],
"Sales", ignore([Gross Sales])
), [Sales]= 0
))
2 Replies
Shravan133Super User
Instead of using just Calendar Week, use a unique week key that combines year and week number, such as 'Date Table'[YearWeek] or build one like:
YearWeek = 'Date Table'[Calendar Year] * 100 + 'Date Table'[Calendar Week]Void Stores =
COUNTROWS(
FILTER(
ADDCOLUMNS(
SUMMARIZE(
'Date Table',
'Date Table'[Calendar Year],
'Date Table'[Calendar Week],
DimItem[Product],
DimStore[Store]
),
"TotalSales", CALCULATE(SUM(FactSales[Gross Sales]))
),
[TotalSales] = BLANK() || [TotalSales] = 0
)
)
v-sdhruvCommunity Support
Hi Anonymous ,
Just wanted to check if you had the opportunity to review the suggestions provided?
Thank you Shravan133 for your prompt response.
If it hasnt, then please consider sending a sample data.
How to provide sample data
Thank You