Forum Discussion
Rolling Total across different weeks
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!
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
2 Replies
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
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
