Forum Discussion

NT2510's avatar
NT2510
Frequent Visitor
5 years ago
Solved

Running Total blanks

Hello,   I have a table with two dates: Close Date, Accounting Date, amount and Month column (Close Date - Accouning Date)+1. I want running total of Amount based on Months.  I used below measure b...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, NT2510 

     

    You may try modifying the measure as below to see if it works.

    RunningAmount2 = 
var _max=CALCULATE(MAX(Sheet1[Mths]),ALLEXCEPT(Sheet1,Sheet1[Year]))
var _min=MIN( MAX('Table'[Value]),_max)
return
    CALCULATE(
        sum(Sheet1[Amount]),
            FILTER(
                ALLSELECTED(Sheet1[Mths]),
                'Sheet1'[Mths]<=_min
            )
    )

     

    Best Regards

    Allan

     

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

v-alq-msft's avatar
v-alq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, NT2510 

 

Based on your description, I'd like to suggest you create a calculated table and a measure as below. The pbix file is attached in the end.

Calculated table:

Table = 
GENERATESERIES(
    MIN(Sheet1[Mths]),
    MAX(Sheet1[Mths]),
    1
)

Measure:

RunningAmount2 = 
COALESCE(
    CALCULATE(
        sum(Sheet1[Amount]),
            FILTER(
                ALLSELECTED(Sheet1[Mths]),
                'Sheet1'[Mths]<=MAX('Table'[Value])
            )
    ),
    0
)

 

Result:

 

Best Regards

Allan

 

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

 

NT2510's avatar
NT2510
Frequent Visitor
5 years ago

Thank you. This helps. 

I have one more question. For 2012, if my data is till Month 20, how to truncate it to display till Month 20. If I modify data, its displaying same result till last month value.

 

Thanks

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, NT2510 

     

    You may try modifying the measure as below to see if it works.

    RunningAmount2 = 
var _max=CALCULATE(MAX(Sheet1[Mths]),ALLEXCEPT(Sheet1,Sheet1[Year]))
var _min=MIN( MAX('Table'[Value]),_max)
return
    CALCULATE(
        sum(Sheet1[Amount]),
            FILTER(
                ALLSELECTED(Sheet1[Mths]),
                'Sheet1'[Mths]<=_min
            )
    )

     

    Best Regards

    Allan

     

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