Forum Discussion

MHein17's avatar
MHein17
Frequent Visitor
2 years ago
Solved

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:   ...
Need Help
  • amitchandak's avatar
    amitchandak
    2 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
2 years ago

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's avatar
MHein17
Frequent Visitor
2 years ago

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