The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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!
Solved! Go to 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 , 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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
264 | |
120 | |
115 | |
83 | |
70 |