Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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 bu...
Need Help
Shravan133's avatar
Shravan133
Icon for Super User rankSuper User
1 year ago

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

    )

)