Forum Discussion

naoyixue1's avatar
naoyixue1
Post Patron
5 years ago
Solved

Help --- Calculate the ending inventory

Hey there,

 

I'm very new to BI, so i might ask something very weird. 😞  I want to calculate the ending inventory from this Sept to the Dec. I'm now having a one table  including work order (production), on hand inventory and demands. Given that, I calculated the inventory movement at first (work order Qty + On hand Inv - Total Demands). and then applied the cumulative concept to get my ending inventory. But because demands data is from this Jan to this Sept, but work order and on hand inventory is only from this Sept to Dec. So, the rolling inventory ( I named as inventory movement) is not right. How should I only implement the calculation for Sept to Dec. 

 

By the way, would you help check whether my calculation dax is right or not? Thanks!

 

Ending Inv = CALCULATE(CALCULATE([Movement],FILTER(ALLSELECTED(FactTable),FactTable[Date]<=MAX(FactTable[Date]))),FILTER(all(FactTable),COUNTROWS(FILTER(FactTable,EARLIER(FactTable[SKU])=FactTable[SKU]))))

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi naoyixue1 

    I think you need cumulative the work order before current month. Your Work order column may be a Measure, however, I don't know its calculate logic.

    You may try this measure:

    Cumulative Work Order = calculate(Sum([Work Order]),Filter('Calendar','Calendar'[Date]<= Max('Calendar'[Date])))

    If this reply still couldn't help you solve your problem, could you show me the calculate logic of your work order measure.

    And I need to know your data model to understand your measure, you can show me a sample data model like the one you are dealing with. Or you can share your pbix file with me by your Onedrive for Business.

     

    Best Regards,

    Rico Zhou

     

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

     

  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi naoyixue1 

    Could you kindly accept the helpful reply as a solution? More people will benefit from it. 

    Best Regards,

    Rico Zhou

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    naoyixue1 , I term of measure you can have a measure with Initial on hand the cumulative incoming and outgoing

     

    new measure =

    [Intial On Hand] + CALCULATE(SUM(Table[work order Qty]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(Table[Demand]),filter(date,date[date] <=maxx(date,date[date])))

    • naoyixue1's avatar
      naoyixue1
      Post Patron
      5 years ago

      Thanks! I'm wondering that whether I should limit the calculation of cumulative demands only from Sept to this Dec, otherwise, those previous demands will eat out Sept on hand inventory and work orders. If that is case, how can i do to narrow the date of demands from the beginning of the year to only the beginning of Sept? Thanks!

      • amitchandak's avatar
        amitchandak
        Super User
        5 years ago

        naoyixue1 ,

        You can use YTD with year end date

        YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"9/30"))

        YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"8/31")) // Give year end date.

         

        Also, prefer date calendar

        To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
        https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

        Appreciate your Kudos.