Cumulative Amount_Spent_Per_Person

[Thomas94401]

Hi, 

This is my problem : 

I have a table of facts with "Indicateurs" :

- One column Month_Survenance

- One column Amount_Spent

- One column Index_Population (integer)

This table is linked with an other table "Population" (connection in both directions) :

- One column Index_Population

- One column Population

And this table is also linked with a Date Table :
- One column Month_Survenance

- One column End_Month_Survenance

I also create a disconnected table "Coding Month" : 

Diff_Min_Slicer	Label_Month
0	M01
1	M02
2	M03
3	M04
4	M05
5	M06
6	M07

The objective is to calculate the average amount spent per person month by month, and then to calculate a column that aggregates these amounts : 

I want to create a measure"Cumulative_Amount_Spent_Per_Person" which should look like this : 

Label_Month	Cumulative_Amount_Spent_Per_Person
M01	267,57
M02	487
M03	696
M04	...
M05	...
M06	...

The measures I have constructed are:

Total_Amount_Spent =

         VAR Slicer_Periode = MIN(Coding_Month[Diff_Avec_Min_Slicer])

         VAR EOMMinSlicerDate = EOMONTH(MIN(Date_survenance[Mois_survenance]), Slicer_Periode)

RETURN

          CALCULATE(

                 SUM(Indicateurs[Amount_Spent]),

                 EOMMinSlicerDate = Date_survenance[End_Month]

           )

---------------------------------------------

Total_Population =

         VAR Slicer_Periode = MIN(Coding_Month[Diff_Avec_Min_Slicer])

         VAR EOMMinSlicerDate = EOMONTH(MIN(Date_survenance[Mois_survenance]), Slicer_Periode)

RETURN

          CALCULATE(

                 SUM(Populations[Population]),

                 EOMMinSlicerDate = Date_survenance[End_Month]

           )

---------------------------------------------

Amount_Spent_Per_Person = DIVIDE([Total_Amount_Spent], [Total_Population], 0)

Thank you in advance for your help! If the measuresI have built are not good, feel free to suggest better measurements !

[v-easonf-msft]

Hi, @Thomas94401 

Are you calculating the cumulative value month by month? If possible, can you provide a simple file for us to test and provide accurate solutions.

You can also check if the solution in this thread could work for you.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Calculating-cumulative-stock-value/td-p/21723... 

https://community.powerbi.com/t5/Desktop/Calculate-cumulative-values-for-selected-months/m-p/2072630 

Best Regards,
Community Support Team _ Eason