How to count rows of items with zero quantity where not all rows are present in fact table

[Anonymous]

Hello,

The issue with the fact table I have to use is that when an item is no longer in stock (0 quantity), after a few consecutive days of zero rows, the table stops recording the other zeros. If the fact table is complete, I could just use something like this

COUNTROWS( FILTER(WhsInvTable, SUM(Quantity) = 0 && WhsInvTable[DateUpdated] = MAX(WhsInvTable[DateUpdated]) ) )

to get the count of items with zero quantity on the latest date.

But in this situation, the fact table looks like this:

Assume latest date is 5/17/2023

DateUpdated	Item	Quantity
5/13/2023	1234	42
5/14/2023	1234	35
5/15/2023	1234	34
5/16/2023	1234	28
5/17/2023	1234	22
5/13/2023	9876	5
5/14/2023	9876	2
5/15/2023	9876	0
5/16/2023	9876	0

Notice there is no row for item 9876 for 5/17/2023. What I came up with is to use a measure to find the last date an item has quantity. If the last date with quantity does not equal the max date, then that item has zero quantity.

Item	Last date with quantity	Max Date	Last Date <> Max Date
1234	5/17/2023 (22)	5/17/2023	no
9876	5/14/2023 (2)	5/17/2023	yes

Get Latest Qty =

var getlatestqty = CALCULATE( MAX(WhsInvTbl[DateUpdated]), FILTER(WhsInvTbl, SUM(Quantity) > 0) )

return

IF(DATEDIFF(getlatestqty, MAX(WhsInvTbl[DateUpdated]), DAY) <> 0, "OOS", "In Stock")

Everything looks okay but how do I take that measure and do a count to see the total number of OOS (Out of Stock) items? Ideally, I want to place a visual which shows the number of OOS items. For example, on 5/17/2023, 1 item is OOS (item 9876).

[johnt75 · Accepted solution]

I think you can create a quantity measure like

Qty =
VAR CurrentDate =
    MAX ( 'Date'[Date] )
VAR Result =
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        'Table'[Date] = CurrentDate,
        REMOVEFILTERS ( 'Date'[Date] )
    )
RETURN
    Result

and then count the number of items with no stock as 

Out of stock =
COUNTROWS ( FILTER ( VALUES ( Items[Item ID] ), [Qty] = 0 ) )

The [Qty] measure will return blank if there are no rows, but blank = 0 so should get picked up by the filter statement.

If you wanted the [Qty] measure to return 0 instead of blank for some reason you could simply return Result + 0.

[Anonymous]

This seems like what I need. But I can't wrap my head around how that REMOVEFILTERS function work. Can you explain a little to me? I've seen other examples using sales which make sense to me but I'm confused with it using on dates.

[johnt75]

Its only really necessary if you have a relationship from your date table to your fact table. If you did have that relationship then when you include columns from the date table in a chart, e.g. year month, then by default it would aggregate all the values for that year month, rather than just showing the value for the last date. By removing the filters from the date table and explicitly placing a filter to show the value for the last date you are overriding that behaviour.