Forum Discussion

StuartSmith's avatar
StuartSmith
Power Participant
4 years ago
Solved

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 lap...
Need Help
ERD's avatar
ERD
Community Champion
4 years ago

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's avatar
StuartSmith
Power Participant
4 years ago

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. 

  • ERD's avatar
    ERD
    Community Champion
    4 years ago

    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/

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    4 years ago

    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.