Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculate availability (dates in columns)

Greetings colleagues.

 

I would like to ask for some help with following problem

I want to calculate availability of product (item ID) for each week

Availability is =  OnHand - Ordered (from today to week in column) + Receipt (from today to week in Column)

Attached you will find Excel file with some sample Data and pbix file with same data.

I managed to solve it if my dates (weeks in my example) are in rows, but i would like to do it when dates are columns and items are rows, this is because in real case i have more than 1000 of items.

 Here is DAX for Available that i used with Dates as rows

 

 

 

Available = 
    var ORD = CALCULATE(SUM(Orders[Ordered]), FILTER(ALL(Dates[Date]), Dates[Date] <= max(Dates[Date])))

    var OH = calculate(sum(OnHand[OnHand]), REMOVEFILTERS(OnHand[OnHand]))

    VAR RCPT = calculate(SUM(Recipept[Reciept QTY]),FILTER(all(Dates[Date]), Dates[Date] <= max(Dates[Date])))
    return
    OH + RCPT - ORD

 

 

 

 

Sample Data:

PBIX file 

Excel with Sample Data 

 

This is how i would like to have my result = 

In each empty cell i would like to calculate Available as OnHand - Ordered (from today to week in column) + Receipt (from today to week in column)

 

Item_idOnHandWeek 32Week 33Week 34Week 35Week 36Week 37Week 38
1000001100       
1000002200       
1000003300       
1000004400       
1000005500       
1000006600       
1000007700       
1000008800       
1000009900       
10000101000       

 

Thank you in Advance.

Need Help
Tips and Tricks
  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous ,

     

    Based on your description, I tried the following test.

    OH = CALCULATE(SUM(OnHand[OnHand]),ALLEXCEPT(Dates,Dates[Week]))
    ORD RT =
VAR ORD =
    CALCULATE (
        SUM ( Orders[Ordered] ),
        FILTER ( ALL ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
RETURN
    ORD


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    Based on your description, I tried the following test.

    OH = CALCULATE(SUM(OnHand[OnHand]),ALLEXCEPT(Dates,Dates[Week]))
    ORD RT =
VAR ORD =
    CALCULATE (
        SUM ( Orders[Ordered] ),
        FILTER ( ALL ( Dates[Date] ), Dates[Date] <= MAX ( Dates[Date] ) )
    )
RETURN
    ORD


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.