Matrix Running Total per Month (Example file included)

Hi StuartSmith , not sure what exactly you want to achieve (in terms of 2 visuals), but here are 2 options:

For the first visual:

headcount_running = 
CALCULATE (
    COUNTROWS ( 'HR Data' ),
    FILTER ( ALLSELECTED ( Dates ), Dates[Date] <= MAX ( 'HR Data'[Start Date] ) )
)

laptops_running = [2022_Total_Laptops] - [headcount_running]

For the 2nd visual another option is possible:

headcount_running_v2 = 
CALCULATE (
    COUNTROWS ( 'HR Data' ),
    FILTER (
        ALLSELECTED ( 'HR Data' ),
        'HR Data'[Start Date] < MAX ( 'HR Data'[Start Date] )
    )
)

laptops_running_v2 = [2022_Total_Laptops] - [headcount_running_v2]

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

I am a Ukrainian living in Ukraine. Please, help us to survive! Please, Ask your government to react!
Here are official ways you can support us financially (accounts with multiple currencies):
https://bank.gov.ua/ua/about/support-the-armed-forces

USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708

Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces

Hi, StuartSmith 

Try this:

Predicted_Headcount_Measure = COUNTROWS('HR Data') + 0

2022_Total_Laptops = 
Var _start=CALCULATE(SUM('Buffer Stock - Current Stock'[Buffer Stock]))+CALCULATE(COUNTROWS('Full Laptop Table'))

var _Running=CALCULATE(COUNT('HR Data'[Start Date]),FILTER(ALLSELECTED('HR Data'),'HR Data'[Start Date]<Min('HR Data'[Start Date])))
return _start-_Running

2022_Remaining_Laptops = 
Var _start=CALCULATE(SUM('Buffer Stock - Current Stock'[Buffer Stock]))+CALCULATE(COUNTROWS('Full Laptop Table'))

var _Running=CALCULATE(COUNT('HR Data'[Start Date]),FILTER(ALLSELECTED('HR Data'),'HR Data'[Start Date]<=Max('HR Data'[Start Date])))
return _start-_Running

Result:

Please refer to the attachment below for details.

Hope this helps.

Best Regards,
Community Support Team _ Zeon Zheng

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi StuartSmith can you share some sample data to work with?  Or better - a pbix with the data model.

Or wondering if there is a different way to get the same result?

ERD & v-angzheng-msft 

Hi Both, sorry for the delay in getting back to you.  Both your solutions worked great on my sample data and simple report, but upon transferring your solution to my really model with more countries, etc. both your solutions had different anomilies with the data.

As an example, Belgium has 57 Laptops, Brazil has 252 & Canada has 176

ERD with your code, it shows Belgium correctly, but Brazil and Canada incorrectly (see below)...

For Belgium it shows the the correct calculation - TotalLaptops (57) - Predicted Headcount for Jan (4) = LaptopsRunning (53)

For Brazil, it shows incorrect, instead of showing the "TotalLaptops" of 252 its showing the "LaptopsRunning (-57) value, despite both using the same measure.  Very odd.

v-angzheng-msft 

And your code is show odd values, but for different countries to the above 😁

I have no idea whats going on and hopefully the above makes sense.  I need to try and figure what the hell is going on 😀