Attached Power BI report contains a “Table” and “Clustered Column Chart” visuals. The report is developed by importing the tables from Excel.
Starting Inventory and Ending Inventory are the main measures required for further analysis in this and other tabs.
Dax Formulae used as below:
Starting Inventory = IF ( SUM ( Inventory[Inventory] ) = 0, [Ending Inventory PM], SUM ( Inventory[Inventory] ) ) Ending Inventory = SUM(Inventory[Inventory]) + SUM ( Actuals[Actuals] ) - SUM ( Forecast[Forecast] ) - SUM ( Forecast[Losses] )
In place of the “SUM(Inventory[Inventory])” in measure Ending Inventory, I should be using [Starting Inventory]. If I use, measure Starting Inventory, it errors out as “Circular Dependency”.
Inventory values will be available until last month only i.e. until September only. From October onwards, the Ending Inventory for previous month has to be loaded to Starting inventory.
Ending Inventory PM = CALCULATE ( [Ending Inventory], DATESINPERIOD ( 'Date Table'[Date].[Date], MAX ( 'Date Table'[Date] ) - 1, -1, MONTH ) )
For October month, the Ending Inventory should be 377,927 + 110,752 – 228,589 – 6,858 = 253,232. But, it wrongly shows as -124,695. Similarly, could not calculate correct values for November and December months.
Could anyone please help me or guide me on how to fix this issue to get the correct results for Oct, Nov & Dec months?
Appreciate your immediate response on this.
Inventory Dashboard.pbix (95.7 KB)