Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

DAX Formula: Inventory QOH in reverse

Disclaimer: I just started working with DAX and I've read and watched a ton of content but I'm still getting hung up on very simple problems.  I learn faster if I can see the solutions to the problem...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi Anonymous

 

I haven't tested this but have you tried reversing the operator?

 

UnitsInStockSimple =
CALCULATE (
    SUM ( 'Transaction Data'[Qty] ),
    FILTER (
        ALL ( 'Date Table'[Date] ),
        'Date Table'[Date] >= MAX( 'Date Table'[Date] )
    )
)
Anonymous's avatar
Anonymous
Not applicable
9 years ago

Thanks,

 

I will try that in the morning at work.  The original problem with that formula is that it doesn't pull 'Inventory Data'[QOH] at all.  The transactions are in: 'Transaction Data' [Qty].  

 

Also, that formula only shows me the total per date, while the sum of all totals does accurately reflect the current QOH, that's not entirely what I want.  I just want to be able to show a date and show the QOH for that date/item. (Maybe I'm displaying that measure incorrectly?)

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi Anonymous,

     

    Could you try the formula(untested) below to see if it works in your scenario? :smileyhappy:

     

    Inventory QOH =
VAR currentQOH =
    MAX ( 'Inventory Data'[QOH] )
VAR maxDate =
    CALCULATE ( MAX ( 'Date Table'[Date] ), ALL ( 'Date Table' ) )
VAR currentDate =
    MAX ( 'Date Table'[Date] )
RETURN
    currentQOH
        - CALCULATE (
            SUM ( 'Transaction Data'[distribution] )
                + SUM ( 'Transaction Data'[Adjustment] )
                + SUM ( 'Transaction Data'[order receipt] ),
            FILTER (
                ALL ( 'Date Table'[Date] ),
                'Date Table'[Date] >= currentDate
                    && 'Date Table'[Date] <= maxDate
            )
        )

     

    Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks, I learned a lot from that formula.  I assume it was meant to be a measure but I tried both and the result is always 12,955,306 for every date and every item.  It appears it might be adding up all QOH in my Inventory Data table?

       

      Also, the only field I have qty in is the 'Transaction Data' [Qty]. there is another field: 'Transaction Data' [Transaction Type] which captures "adjustment", "Receipt", etc...  I changed the 3 lines on your formula to : SUM ( 'Transaction Data'[Qty] ),

       

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi Anonymous,

         

        Could you share a sample pbix file, so that we can better assist on this issue? You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. :smileyhappy:

         

        Regards