DAX count consecutive days without stock

[Gertjan]

Hi there, 

I have a calculated column that counts the number of consecutive days an item is out-of-stock. It works if there isn't that much data in the tables. However, when there is more data in the tables, the performance is very bad or I even get an error message (Memory error: Memory Allocation failure . Try simplifying or reducing the number of queries). 

The data model consists of a Keys table and a FactTable. The Keys table creates unique keys for each unique item + date combination, so it has three columns: item, date, key. The FactTable stores data on each key, such as the current stock level. 

Are there any recommendations on how I can improve the performance of this calculated column with the same data structure? 

#Days out-of-stock =
IF (
    'FactTable'[Stock] > 0,
    0,
    VAR vCurrentDate =
        RELATED ( Keys[Date] )
    VAR vCurrentID =
        RELATED ( Keys[Item] )
    VAR vPrevTbl =
        FILTER (
            'FactTable',
            RELATED ( Keys[Date] ) <= vCurrentDate
                && RELATED ( Keys[Item] ) = vCurrentID
                && 'FactTable'[Stock] <= 0
        )
    VAR vPrevDate =
        MAXX (
            FILTER (
                vPrevTbl,
                VAR vCD =
                    RELATED ( Keys[Date] )
                VAR r =
                    MAXX (
                        FILTER ( vPrevTbl, RELATED ( Keys[Date] ) < vCD ),
                        RELATED ( Keys[Date] )
                    )
                RETURN
                    vcd - 1 <> r
            ),
            RELATED ( Keys[Date] )
        )
    RETURN
        vCurrentDate - vPrevDate + 1
)

Thanks in advance!

[v-janeyg-msft · Accepted solution]

Hi, @Gertjan 

Obviously, there is nothing wrong with your code and the code doesn't look like there's anything to simplify. Mainly because you have a large amount of data.

For your case, I have these suggestions:

• Filter the date column or other columns in PQ to control the amount of data obtained in the report each time.
• Change the calculated column to measure, it will reduce memory calculation, but it needs to be created according to the context of the visual.
• Change to a more advanced machine to create the report, you can try to change the setting first.

          

If you are interested in the above suggestions but have problems, please add information, or share a sample file, and I will help you solve it.

Did I answer your question? Please mark my reply as solution. Thank you very much.
If not, please feel free to ask me.

Best Regards,
Community Support Team _ Janey

[ValtteriN]

Hi,

Here is one approach to this:

OutOfstock = var _item = MAX(ItemExample[Item])

var maxdate = MAX('Calendar'[Date])

var mindate = MIN('Calendar'[Date])

var vtable = countrows(FILTER(ALL(Stock),Stock[Item]=_item))

var _dates = DATEDIFF(mindate,maxdate,DAY)+1

return

_dates-vtable

Data:

End result:

The idea is pretty simple: we calculate days that we have items in stock and substract this from all the dates during the period we are interested in. So here 1.1.2021-8.1.2021 = 8 days -> for item1 we have stock on 6 days -> 2 days out of stock. If you have 0 values for days in your stock table you can add this to the FILTER:

var vtable = countrows(FILTER(ALL(Stock),Stock[Item]=_item&&Stock[value]<>0))

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[Gertjan]

Thanks for your quick reply, however I meant the number of consecutive days. I have now edited my initial post to make it a bit more clear.