Matrix Running Total per Month (Example file included)

[StuartSmith]

I have a matrix that shows values per month... the predicted heacount, available laptops and then remaining laptops (laptops - headcount).  Is there a way to get a running total for the remaining laptops value, 

So as an example, for Jan the "Remaining Stock" is "3902".  This value then needs to be "Available Stock" for Feb, and so on through the months. Hope that makes sense.  Any ideas? Thanks in advance.

Running Monthly Total (Link to example file in Dropbox)

[StuartSmith]

@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 😀

[v-angzheng-msft]

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.

[ERD]

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

[StuartSmith]

Thanks, that worked as expected.  I then decided to duplicate the full report tab into Q1, Q2 & Q3 and then simply show the months data for those quaters. Q1 works perfect, but for Q2 & Q3, then figures dont carry across and simply restart.

As an example, at the end of Q1, there are 24 laptops remaining.

Then for Q2 it should say that there are 24 Remaining Laptops,but the "Total Laptops" value has reset.

and the same with Q3?  I thought simply duplicating the tab and filtering the months would work, but I guess not 🤔  Iwill try and figure it out, but any help is appreciated.

Updated Demo File with Q Tabs. 

[v-angzheng-msft · Accepted solution]

Hi, @StuartSmith 

Please still use the formula I provided above, but replace the ALLSELECTED function with the ALL function.

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.

[ERD]

@StuartSmith , you can use these 4 measures:

your initial for predicted amount:

_Predicted_Headcount_Measure = COUNTROWS('HR Data') + 0

and 3 more:

_HeadcountTotal =
VAR dt = MAX ( 'HR Data'[Start Date] )
VAR hc = CALCULATE ( [_Predicted_Headcount_Measure], Dates[Date] <= dt )
RETURN
    hc

_TotalLaptops =
VAR initial = CALCULATE ( SUM ( 'Buffer Stock - Current Stock'[Buffer Stock] ) )
  + CALCULATE ( COUNTROWS ( 'Full Laptop Table' ) )
VAR prev_hc = CALCULATE ( [_HeadcountTotal], DATEADD ( Dates[Date], -1, MONTH ) )
RETURN
    initial - prev_hc

_LaptopsRunning = [_TotalLaptops] - [_Predicted_Headcount_Measure]

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! Ethical standards are more important than generating Profits. Keeping doing business in russia and belarus equals being co-responsible for the Ukrainian people death... Please, Ask your government to react! Any other country might be the next one!
Here are official ways you can support us financially (accounts with multiple currencies):
1) 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
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

[StuartSmith]

Thanks for looking at my problem.  Hopefully the image will explain better what I'm trying to achieve.

Also, ignore this table...

[ERD]

@StuartSmith , use the first option. Calculations are as you've shown

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

[StuartSmith]

Thanks, works great.  Developing the solution, is there a way to get the "Laptop_Running" value from the previous month to be the total of the following month.  So as an example, from Jan the "Running Laptop" figure is 28, therefore the "Total Laptops" vaule for Feb should be 28, and so on through the month. So the "Total Laptops" figure is reduced by the previuous months headcount.  Hope that makes sense.

[StuartSmith]

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

[littlemojopuppy]

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

[StuartSmith]

Thanks for getting back to me.  Find a "Dropbox Link" to a demo file, with a similar setup to the working file.

Running Monthly Total 

So in the attached example, for Jan there are 31 "Total Laptops" available with 3 "Predicted Headcount", leaving 28 "Remaining Laptops".  Therefore, for Feb, the "Total Laptops" column should show the same figure as Jan's "Remaining Laptops" figure of 28, and Febs "Remaining Laptops" value should be 26 (Feb Total Laptops - Feb's Predicted headcount.  😀  

Hope that makes sense, but basically just want a running total of available laptops after the months new starters.