Forum Discussion

ErinT333's avatar
ErinT333
New Member
3 years ago
Solved

Count Number of Weeks where Negative value

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.

 

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ErinT333
      New Member
      3 years ago

      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.  

       

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      ErinT333
      New Member
      3 years ago

      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.