Expression to get balance from Last month as Inventory for current month

[DJ55]

Hi Everyone,

I am trying to generate expression to get Balance from Apr 2023 As as input to inventory of  May 2023

This complete data is in Pivot chart. I can not use other charts to display data as data structured in that way and it is business requirement.

V_Balance = Stock+Inventory+  Upcoming Quantity +Blocked Qty+ Demand+R Inv

All the data is flowing in single columns against Mat type

Can anyone please provide solution for this?  It will be really helpfull

Mat Type	Apr/2023	May/2023	Jun/2023
Stock	1,000.00
Inventory	100,000.00	51,000.00	66,020.00
Upcoming Quantity	-	40,000.00	-
Blocked Qty	-	20.00	-
Demand	-   50,000.00	-   25,000.00	-     2,500.00
R Inv	-	-	-
Balance	51,000.00	66,020.00	63,520.00

[mayuringale25]

Hi @DJ55 

1. First, you will need to Define a variable that will store the inventory value for May 2023:

Expression:LET v_inventory_may_2023 = sum({<MonthYear={'May 2023'}>} Inventory);

2/Then you will need to create a variable that will calculate the balance as of April 2023:

Expression:V_Balance = sum({<MonthYear={'Apr 2023'}>} Stock) + sum({<MonthYear={'Apr 2023'}>} Inventory) + sum({<MonthYear={'Apr 2023'}>} UpcomingQuantity) + sum({<MonthYear={'Apr 2023'}>} BlockedQty) + sum({<MonthYear={'Apr 2023'}>} Demand) + sum({<MonthYear={'Apr 2023'}>} RInv) - $(v_inventory_may_2023);

Note that you may need to adjust the syntax based on the field names and date formats in your data model