topic Re: Compute total inventory value irrespective of the transactions on any given day in DAX Commands and Tips

Compute total inventory value irrespective of the transactions on any given day

nidhi_ifi — Wed, 12 Aug 2020 18:30:07 GMT

There are X number of items(For eg: A,B,C) being managed in the records of the inventory. For any given date the record in the database is only available for those items that were affected due to sale or purchase transaction.

The goal is to produce this report for any given date which is the total value of inventory irrespective of any transaction on that day.

In order to arrive at this it will be necessary to compute value of each item irrespective of the transaction on that date:

I need help with the dax query to achieve the above goal.

Thanks.

Re: Compute total inventory value irrespective of the transactions on any given day

Anonymous — Wed, 12 Aug 2020 20:03:16 GMT

// If you store everything in // one table T... // this will give you the value // on the last visible day // in the current context. [Total Value] = var __lastVisibleDay = MAX( T[Date] ) var __result = SUMX( VALUES( T[Item] ), MAXX( TOPN(1, CALCULATETABLE( T, T[Date] <= __lastVisibleDay, ALL( T[Date] ) ), T[Date], DESC ), T[Value] ) ) return __result

Re: Compute total inventory value irrespective of the transactions on any given day

nidhi_ifi — Thu, 13 Aug 2020 10:02:59 GMT

The solution gives the sum of the values for the items whose transactions have taken place on a particular day. It does not include the items that did not have any transactions on that day. The goal is to compute the total value of the inventory covering all items irrespective of whether they were transacted on the given day. Please refer the example from the images.

Re: Compute total inventory value irrespective of the transactions on any given day

Anonymous — Thu, 13 Aug 2020 10:18:39 GMT

Just change VALUES( T[Item] ) to ALL( T[Item] ) and you'll get what you want.