I am facing an issue where I want to be able to report on ending inventory quantity. The problem is that the SQL table that stores this data only updates if there is activity, otherwise when you run a report from the system, it goes back to the last date that it was supposed to have that balance and repeats.
As an example, say I have product A has a beginning balance of 100, 45 get taken out of inventory for sales and transfers, and the ending balance of 55 remains at the West site on 4/25/24. However, on 4/26/24, there are no purchases, sales, or transfers. This continues for 4 days. On 4/29/24, I run a report from the system, and it states a beginning balance of 55 on 4/29/24 and an ending balance of 55 on 4/29/24 at a certain site.
The system creates a record (row) on 4/25/24 for 100, 45 and 55 respectively. However, after 4/25, no new records (rows) get entered to the SQL table, and I want to report the ending balance on 4/29/24 as 55.
Additional Notes: Products can have a zero beginning, zero activity and zero ending balance. Products can also have a beginning and ending balance with zero activity. These might be manually created, but I’m not sure.
I wrote this measure to get the inventory at the end of each day.
Last Non-zero Inventory Sample Data.xlsx (172.0 KB)
Last non-zero inventory sample.pbix (95.0 KB)
End Inventory Qty =
CALCULATE(
SUM(Fact_Inventory[End_Qty] ),
Dim_Calendar[Date] = MAX( Dim_Calendar[Date] )
)
I did some research online and found that I might be able to use crossjoin to build a virtual table , and write a measure to get the previous balance, but I haven’t had any luck. I’ve tried LastNonBlankValue and qty <> 0, but it wouldn’t producing rows in my table for dates after 4/25/24.
When I print the report from the system, it displays the balance through the current date by retrieving the previous balance. Does anyone know how to do this with Power BI?
I wrote another measure. It shows the days from 4/25/24 – 4/29/24 but also the rows missing data are being returned as 0. I know it’s due to the 0 in the if statement after the return, but I don’t know if I’m on the right track and I can’t seem to get anything to work.
Ending Inventory Balance test =
VAR MaxDate = TODAY()
VAR AllCombinations =
CROSSJOIN ( FILTER( dim_Calendar, dim_Calendar[Date] <= MaxDate), CROSSJOIN ( dim_Sites, dim_Products ) )
RETURN
SUMX (
AllCombinations,
VAR EndingBalance =
CALCULATE (
MAX ( fact_Inventory[End_Qty] ),
FILTER (
fact_Inventory,
fact_Inventory [FuelInv_Date] = dim_Calendar[Date]
&& fact_InventoryFuelInv_Site_Key] = ‘dim_Sites’[Site_Key]
&& fact_Inventory[FuelInv_Prod_Key] = dim_Products[Prod_Key]
)
)
RETURN
IF ( ISBLANK ( EndingBalance ), 0 , EndingBalance )
)
Thank you
