cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Power Participant

## Matrix Running Total per Month (Example file included)

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)

1 ACCEPTED SOLUTION
Community Support

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.

12 REPLIES 12
Power Participant

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.

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 😀

Community Support

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.

Super User

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.

Here are official ways you can support us financially (accounts with multiple currencies):

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
PURPOSE OF PAYMENT: for crediting account 47330992708

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Power Participant

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.

Community Support

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.

Super User

@StuartSmith , you can use these 4 measures:

``_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):
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
PURPOSE OF PAYMENT: for crediting account 47330992708
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Power Participant

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

Also, ignore this table...

Super User

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

Here are official ways you can support us financially (accounts with multiple currencies):

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
PURPOSE OF PAYMENT: for crediting account 47330992708

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

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Power Participant

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.

Power Participant

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

Community Champion

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

Power Participant

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

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.