Rolling Total across different weeks

[MHein17]

I have 4 columns that interact with each other: Total Actual Occupied Units, Total Projected Move-Ins, Total Projected Move-Outs and Total Project Occupied Units. Below is the DAX for each:

Total Actual Occupied Units:=CALCULATE(DISTINCTCOUNT(Units[UnitID]),FILTER(Units,Units[Flag] = "Yes"))

Total Projected Move-Ins:= CALCULATE(DISTINCTCOUNT(Proj[Unit ID]), FILTER(Proj,Proj[Flag] = "Move-In"))

Total Projected Move-Outs:= CALCULATE(DISTINCTCOUNT(Proj[Unit ID]), FILTER(Proj,Proj[Flag] = "Move-Out"))

Total Projected Occupied Units:= [Total Actual Occupied Units] + [Total Projected Move-Ins] - [Total Projected Move-Outs]

Currently For 3/17/2024, there are 5,062 occupied units + 4 Move-Ins - 12 Move-Outs = 5,054 projected occupied units. For 3/24/2024, I would like the 5,054 projected occupied units from the first week to carry over as the Actual Occupied Units (This will be renamed). So for week 3/24/2024, the math should work out to be 5,054 occupied units (from the first week 3/17/2024) + 8 Move-Ins - 16 Move Outs = 5,046, instead of it showing 5,054. Any help is greatly appreciated!

[amitchandak · Accepted solution]

@MHein17 , for such cases we use cumulative measures

example with date table used in measure, visual and filter

[Total Actual Occupied Units] + calculate( [Total Projected Move-Ins] - [Total Projected Move-Outs], filter(all(Date), Date[Date] <= Max(date[Date]) ) )

or

 calculate([Total Actual Occupied Units]+ [Total Projected Move-Ins] - [Total Projected Move-Outs], filter(all(Date), Date[Date] <= Max(date[Date]) ) )

Same as inventory onhand calculation

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

[MHein17]

Hi Thank you for this. What if there are weeks where there is no Move Ins or Move Outs? These weeks revert back to the first week value