Count Number of Weeks where Negative value

[ErinT333]

Not sure if this can be done in Power Query or Dax. 

I have an inventory dashboard calculating Available inventory over time so we can see when we need to reorder or possible future stock outs.  I have 2 datasets.  Date Table and Union of all my transactional data for each part.  

I created measures summing up each set of data.  Committed, OnHand, and On Order. I also created a measure summing up OnHand+OnOrder-Committed to get my Available.

Then I created running totals off of those measures.  The calculations are working great and are accurate.  

My problem comes in when I need to count the number of weeks from today forward that might have a negative Available Qty.

Basically it should be the number 13 going by this below table.  I just cannot figure out to get that number by Part....Any help would be appreciated.

[Anonymous · Accepted solution]

Hi @ErinT333 ,

Please try this measure.

Measure = 
COUNTROWS(
    FILTER(
        SUMMARIZE(
            'Table',
            'Table'[Part],
            'Table'[First of Week],
            "@total",[RunningTotalAvaliable]
        ),
        'Table'[First of Week]>=TODAY()
            && [@total] < 0
    )
) + 0

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[ErinT333]

Your code works in regard to which weeks are negative when there is a transaction.  This is good and will help me get some of where I need to be.  I just need to see if the boss wants those other weeks to still be counted as well or if this will be enough.  Thank you so much for getting me this far

CountRows for this one was 2 as expected based on the transactions.

[Anonymous]

let t= PriorStepOrTableName, Today = Date.From(DateTime.LocalNow())in

Table.AddColumn(PriorStepOrTableName, "Count", each Table.RowCount(Table.SelectRows(t, each [First of Week] > Today and [RunningTotalAvailable] < 0)))

--Nate

[ErinT333]

Is this a measure i need to add or somethign i need to add to a table as a column?  Don't actually have a table with the running total as a column.  I have my table that i created running total measures from.  That table is linked to a date table and a part information table.