Cummulative sum by month and year

[DanChaos]

Hi everyone, I'm having trouble getting the commulive sellings by month on my data. I have tried several options but the calculated field does not add the value of the last month.

Heres ha picture of what I'm trying to achive and the formula used on the calculated field:

I created the calendar table with the dates:

I also triyed this expression with no results either:

Año Acumulado = CALCULATE ( SUM ('Ene-Sep2017'[VALOR]);FILTER(ALL(Calendario[fecha]);Calendario[fecha]<= MAX(Calendario[fecha])))

Thanks for the help in advance, I'm new to power BI and just discovered this DAX world lenguage.

Daniel.

[Abduvali · Accepted solution]

@DanChaos

OK, can you please tell me why are you using Calendar table to calculate your total sum but then in your table Month column is from your Ene-Sep 2017 table???

• why not just use your Ene-Sep 2017 date field in calculating your total that way it will be easier for you:
• Año Acumulado = CALCULATE ( SUM ('Ene-Sep2017'[VALOR]),FILTER(ALL('Ene-Sep2017'),'Ene-Sep2017'[fecha]<= MAX('Ene-Sep2017'[fecha])))

Hope this helps if not send me some sample data and I will help you.

Regards

Abduvali

[Abduvali]

Hi @DanChaos,

Try this:

• Año Acumulado = CALCULATE ( SUM ('Ene-Sep2017'[VALOR]),FILTER(ALL(Calendario),Calendario[fecha]<= MAX(Calendario[fecha])))

See if that works now =D

Regards

Abduvali

[DanChaos]

the values are not adding up, shows the same value as the originial:

[Abduvali · Accepted solution]

@DanChaos

OK, can you please tell me why are you using Calendar table to calculate your total sum but then in your table Month column is from your Ene-Sep 2017 table???

• why not just use your Ene-Sep 2017 date field in calculating your total that way it will be easier for you:
• Año Acumulado = CALCULATE ( SUM ('Ene-Sep2017'[VALOR]),FILTER(ALL('Ene-Sep2017'),'Ene-Sep2017'[fecha]<= MAX('Ene-Sep2017'[fecha])))

Hope this helps if not send me some sample data and I will help you.

Regards

Abduvali

[DanChaos]

It worked, thank you!!.

I used other table following the suggestions in other post about having a diferent table with the dates. In case I wanted to do a cummulative sum by year how does the formula change?.

[Abduvali]

@DanChaos,

Great that it worked =D

• what do you mean by cumulative? is it running total you talking about?

Regards

Abduvali

[DanChaos]

Hi again, I just noticed that the sum is not working well, here's the pic:

Could it be the date format?, we use day/month/year; US usually uses month at the start.

I took 2 months of data in this file so you can work with it: data sample

thanks again for the effort.

Daniel

[Abduvali · Accepted solution]

Hi @DanChaos,

Use this to calculate your running total:

• VALOR running total in MES =
                                                   CALCULATE( SUM('Hoja1'[VALOR]),
                                                                        FILTER( ALLSELECTED('Hoja1'[mes]),
                                                                                   ISONORAFTER('Hoja1'[mes], MAX('Hoja1'[mes]), DESC)
                                                                                      )
                                                                         )

Hope this will solve your issue =D

Regards

Abduvali 

[DanChaos]

Thank you, that did the trick!

[Abduvali]

You are welcome =D

Regards

Abduvali