Skip to main content
cancel
Showing results for 
Search instead 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

Reply
StuartSmith
Power Participant
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, 

 

2022-03-05_17-03-00.png

 

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

Hi, @StuartSmith 

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

vangzhengmsft_0-1646963013135.png

Result:

vangzhengmsft_1-1646963084697.png

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.

View solution in original post

12 REPLIES 12
StuartSmith
Power Participant
Power Participant

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

ERD 1.png

ERD 2.png

ERD 3.png

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 😁

CTS1.png

 

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
Community Support
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:

vangzhengmsft_0-1646895531077.png

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
Super User
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]

ERD_0-1646595926565.png

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

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!

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.

Q1.png

 

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

Q2.png

 

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. 

Hi, @StuartSmith 

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

vangzhengmsft_0-1646963013135.png

Result:

vangzhengmsft_1-1646963084697.png

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.

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

 

ERD_0-1646935916910.png

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/

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!

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

2022-03-06_21-38-27.png

 

Also, ignore this table...

2022-03-06_21-53-00.png

 

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

ERD_0-1646642953967.png

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

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!

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.

 

2022-03-09_15-45-11.png

StuartSmith
Power Participant
Power Participant

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

littlemojopuppy
Community Champion
Community Champion

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

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. 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.