Inventory report with open balances, sum of transactions by trx types, and with close balances

[LISAN_AL_GAIB]

Hi,

Is it possible to build this report in Power BI?

There is one table for inventory transactions (about 1mln records so far).

I need to build report looking like this. Summarized by Item/Warehouse combination.

User should be able to change report dates with a slider.

Thanks,

L.G.

[amitchandak · Accepted solution]

@LISAN_AL_GAIB , You have to create that many measures. , later you can use calculation group two have dual header.

While all Transaction meausre will be sum, Opening and closing would be cummulative

14 = calculate(Sum(Table[Quantity]), filter(Table, Table[TraxType]= 14))

Same for others

Opening =

 CALCULATE( [10]-[11]+[12] -[13]-[14]+[15],filter(date,date[date] <minx(date,date[date]) ) )

closing=

 CALCULATE( [10]-[11]+[12] -[13]-[14]+[15],filter(date,date[date] <=maxx(date,date[date]) ) )

Power BI Inventory On Hand: https://youtu.be/nKbJ9Cpb-Aw

Calculation Groups- Measure Slicer, Measure Header Grouping, Measure to dimension conversion. Complex Table display : https://youtu.be/qMNv67P8Go0

[LISAN_AL_GAIB]

Hi amitchandak,

Thank you for reply!

I will try it this way.

Regards,

L.