How to add the result of one month of a measure in the next month?

[walkra]

Hi, how are you.

In one measure the value of each month is accumulated. I am wanting to have the sum of that accumulated from the last 6 months (in each month), in a Visualizations Table of a Report.

The measure is: 

MovimientoEnAlmacen = (CALCULATE(SUM(TMSEGX[ImporteML]); DATESINPERIOD(TablaDeFechas[Date];lastdate(TablaDeFechas[Date]);-100;YEAR); TMSEGX[DebeHaber]="S"))-

(CALCULATE(SUM(TMSEGX[ImporteML]); DATESINPERIOD(TablaDeFechas[Date];lastdate(TablaDeFechas[Date]);-100;YEAR); TMSEGX[DebeHaber]="H"))

For Example, (in the Report): 

Año          Mes          MovimientoEnAlmacen        Suma (??)

2017         enero       10000                                    10000

2017         febrero     12000                                    22000

2017         marzo       15000                                    37000

2017         abril          10000                                    47000

2017         mayo         500                                       47500

2017         junio          500                                       48000

2017         julio           100                                       38100

2017         agosto       5000                                     31100

As in each month the total of the rest of the months is added up, I do not know how to add that total amount (of each month) of the last six months.

If you can help me, I would be very grateful. Thank you, very much. 

(I'm sorry my English). 

[walkra]

To clarify (I forgot the MonthMove column): 

Año          Mes          MonthMove               MovimientoEnAlmacen        Suma (??)

2017         enero            0                                 10000                                   10000

2017         febrero         2000                            12000                                  22000

2017         marzo           3000                            15000                                  37000

2017         abril             -5000                            10000                                  47000

2017         mayo           -9500                               500                                   47500

2017         junio               0                                   500                                   48000

2017         julio             -400                                100                                    38100

2017         agosto         4900                              5000                                   31100

[v-danhe-msft]

Hi @walkra,

Based on my test, you need to add an index column for your data view in query editor:

https://stackoverflow.com/questions/45715963/creating-an-index-column-for-power-bi

And try below measure:

Measure 2 = SUMX(FILTER(ALL('yourtable'),'yourtable'[Index]<=MAX('yourtable'[Index])),'yourtable'[MovimientoEnAlmacen])

Result:

Regards,

Daniel He

[walkra]

Hi, Daniel. Thank you very much for your answer.

Unfortunately I must be doing something wrong because it did not work.

I did a test with a smaller table:

Centro	Almacen	AñoMesFechaDocumento	TotalMovimientos	Index
A010	A010	1/1/2017	5000	1
A010	A010	10/1/2017	4000	2
A010	A010	20/1/2017	1000	3
A010	A010	1/2/2017	1000	4
A010	A010	28/2/2017	1000	5
A010	A010	30/3/2017	3000	6
A010	A010	1/4/2017	-1500	7
A010	A010	10/4/2017	-1500	8
A010	A010	11/4/2017	-2000	9
A010	A010	15/5/2017	-4500	10
A010	A010	16/5/2017	1000	11
A010	A010	18/5/2017	-500	12
A010	A010	19/5/2017	-1000	13
A010	A010	30/5/2017	-4500	14
A010	A010	1/6/2017	0	15
A010	A010	1/7/2017	-400	16
A010	A010	1/8/2017	4900	17

I used a smaller measure of the "MovimientoEnAlmacen" but with similar characteristics:

MovimientoEnAlmacen2 = CALCULATE(SUM(TablaSumaPrueba[TotalMovimientos]); DATESINPERIOD(Calendario[Date];lastdate(Calendario[Date]);-100;YEAR))

And your measure: 

Measure B = SUMX(FILTER(ALL('TablaSumaPrueba');'TablaSumaPrueba'[Index]<=MAX('TablaSumaPrueba'[Index]));'TablaSumaPrueba'[MovimientoEnAlmacen2])

But the result is in both columns the same result: 

Here the report: 

The result in "Measure B" in the reporte isn´t the same that in the column "Measure 2" of your report. 

What can be wrong?

Again, Thank you, very much

[v-danhe-msft]

Hi @walkra,

Due to I could not reproduce your problem, could you please share your pbix file to have a test if possible? You could upload your report to your OneDrive or Dropbox and send the link here.

Regards,

Daniel He 

[walkra]

Hi, Daniel, How are you? I hope that you are fine. 

Here you have the link:

https://1drv.ms/f/s!At33Gf20rWIrelarDHcl9ULspvA

In the Publico folder upload "acumulado.pbix" and two excels file (in case you need them: Calendar and the main table). 

(I was thinking if the problem is not due to some configuration that I have in my Power BI or in my pc?).

Thank you, very much. 

[v-danhe-msft · Accepted solution]

Hi @walkra,

I have tested your pbix file and I think the problem may due to the row context with your data, you could try to swith off the

hierarchy:

And try to modify the Measure B as below:

Measure B = SUMX(FILTER(ALLSELECTED('Calendario'[Año - MESNumb]),'Calendario'[Año - MESNumb]<=MAX('Calendario'[Año - MESNumb])),[MovimientoEnAlmacen2])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[walkra]

Excelente!!!

Thank You Very Much. 

[walkra]

Hi, Daniel, How are you?. I hope that you are fine.

Sorry for the inconvenience again, but there was a problem. The logic that I indicated is correct but has a problem: if it is filtered, for example, four consecutive months, the cumulative calculation is of those four months that were chosen. How could it be programmed so that when it filters, it continues to accumulate from the first day, it still leaks for a few months? Please, tell me if I open another ticket (but, please, if you can help me).

Thank You!!