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 but somehow its still returning blanks or zero amount.

 

Here is the link to report: Running Total Blanks

 

I searched through many forums but somehow couldnt able to figure this out.

 

Thanks

 

 

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.

8 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    5 years ago

    NT2510 

    Can you please clarify what you are trying to calculate? A depiction would be very helpful. Thanks.

    • NT2510's avatar
      NT2510
      Frequent Visitor
      5 years ago

      This is how output should look like. In PoweBI, there are some blanks which should show last non-blank value.

       

       

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    5 years ago

    Hi,

    I do not understand your requirement.  How can you have more than 12 months in the column labels?  What does that mean?

    • NT2510's avatar
      NT2510
      Frequent Visitor
      5 years ago

      I have updated file with meaure that shows how Mths column is calculated.

  • v-alq-msft's avatar
    v-alq-msft
    Community 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
        Community 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.