Calculating delta of product Quantity between two dynamically selected dates

[sridharpolina]

I am trying to calculate the delta of product quantity for two dynamically selected dates. I have a table populating the beginning, ending and the delta inventory for both the dates. Now I am having trouble calculating the delta between DI2-DI1 as shown in the image. The date selection is dynamic and User selected. The sample data and the .pbix files are uploaded to Goggle Drive. Any help is appreciated.

[v-yangliu-msft · Accepted solution]

Hi @sridharpolina ,

Here are the steps you can follow：

1. Create calculated table.

Table = SUMMARIZE('Inventory Changes','Inventory Changes'[as_of_date])

Result:

2. Replace the second slicer with [as_of_date] of the newly created table

3. Select the slicer above, select Edit interactions of Format, and select the Filter flags in both tables

4. Create measure.

_begin = 
SUMX(FILTER(ALL('Inventory Changes'),[as_of_date] in SELECTCOLUMNS('Table',"1",[as_of_date])&&[product_identifier1]=MAX([product_identifier1])),[opening_inv])

_end = 
SUMX(FILTER(ALL('Inventory Changes'),[as_of_date] in SELECTCOLUMNS('Table',"1",[as_of_date])&&[product_identifier1]=MAX([product_identifier1])),[closing_inv])

Delta_Inventory1 =
[_end]-[_begin]

Delta_Inventory2 =
SUM('Inventory Changes'[Delta_Inventory])-[Delta_Inventory1]

5. Result.

 Best Regards,

Liu Yang

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