Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Fixed cumulative sum (current month)

I need to calculate an accumulated sum and use the accumulated value for the current month as a variable to add the value column for the other previous months. Example:

 

MêsValorAcumuladoResultado
Janeiro 101080+10=90
Fevereiro102080+20=100
Março103080+30=110
Abril104080+40=120
Maio105080+50=130
Junho106080+60=140
Julho107080+70=150
Agosto108080+80=160
Setembro109080+90=170
Outubro1010080+100=180
Novembro1011080+110=190

 

I created a measurement to return the value for the month of August, but when I put the measurement on the graph, I have the result below.

 

MêsValorAcumuladoResultado
Janeiro 101010+10 =20
Fevereiro102010+20=30
Março103010+30=40
Abril104010+40=50
Maio105010+50=60
Junho106010+60=70
Julho107010+70=80
Agosto108010+80=90
Setembro109010+90=100
Outubro1010010+100=110
Novembro1011010+110=120

 

I need the accumulated value of the current month (August) to add with the value column for each month.
can you help me?

  • Anonymous's avatar
    Anonymous
    6 years ago

    HI Anonymous ,

     

    Create an index column in Power Query

     

     

     

    Then create these Calculated Columns

     

    Ac = SUMX(FILTER('Table','Table'[Index] <= EARLIER('Table'[Index])),'Table'[Valor])

     

    RE = 
var Augusto = SUMX(FILTER('Table','Table'[Mês] = "Agosto"),'Table'[Ac])
RETURN
Augusto + 'Table'[Ac]

     

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous ,

     

    Create an index column in Power Query

     

     

     

    Then create these Calculated Columns

     

    Ac = SUMX(FILTER('Table','Table'[Index] <= EARLIER('Table'[Index])),'Table'[Valor])

     

    RE = 
var Augusto = SUMX(FILTER('Table','Table'[Mês] = "Agosto"),'Table'[Ac])
RETURN
Augusto + 'Table'[Ac]

     

     

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

     

    Not very clear.

     

    Can you share the expectd output?

     

    Regards,
    Harsh Nathani
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      hi, Anonymous I edited the post, was it clearer?