Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Compete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.

Reply
Gertjan
Frequent Visitor

DAX count consecutive days without stock

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 ( vPrevTblRELATED ( Keys[Date] ) < vCD ),
                        RELATED ( Keys[Date] )
                    )
                RETURN
                    vcd - 1 <> r
            ),
            RELATED ( Keys[Date] )
        )
    RETURN
        vCurrentDate - vPrevDate + 1
)

Thanks in advance!

1 ACCEPTED SOLUTION
v-janeyg-msft
Community Support
Community Support

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.

          vjaneygmsft_0-1643868258199.png

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

 

View solution in original post

3 REPLIES 3
v-janeyg-msft
Community Support
Community Support

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.

          vjaneygmsft_0-1643868258199.png

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

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:
ValtteriN_0-1643366088813.png

 

End result:

ValtteriN_1-1643366103900.png



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!






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.