topic Re: Cumulative SUM by Month by contrat in DAX Commands and Tips

Cumulative SUM by Month by contrat

Lefuneste57 — Sun, 18 Aug 2019 12:23:53 GMT

Hi, sorry for my english, i'm french.

I have a table like this and i would calculate the column "Montant cumulé". So, for each Month, the cumulative amount per Month and per N°Contrat.. Can you help me to found the DAX formule for the yellow Column plesae ?

Thanks for helping.

Re: Cumulative SUM by Month by contrat

Anonymous — Sun, 18 Aug 2019 12:38:47 GMT

[Montant cumule] =
var __noContrat = T[N°Contrat]
var __currentDate = T[Date]
var __cumule =
	SUMX(
		FILTER(
			T,
			T[N°Contrat] = __noContrat
			&&
			T[Date] <= __currentDate
		),
		T[Montant]
	)
return
	__cumule

Re: Cumulative SUM by Month by contrat

Lefuneste57 — Sun, 18 Aug 2019 14:18:44 GMT

Hi, thanks

but i have another dufficult, i have try your solution but when i have more amount in the same month with négative value, it's no good.

For exemple, with this table :

I must have for N°Contrat C002 a cumulative value of 130.

But with your formule, i have 260 :

Thank you very much for your help.

Re: Cumulative SUM by Month by contrat

Anonymous — Sun, 18 Aug 2019 14:38:42 GMT

What I've given you is a CALCULATED COLUMN, not a measure. You did not say a single word about a measure.

Best
Darek

Re: Cumulative SUM by Month by contrat

Lefuneste57 — Sun, 18 Aug 2019 14:42:27 GMT

I'm sorry.

Can you help me ?

Re: Cumulative SUM by Month by contrat

Anonymous — Sun, 18 Aug 2019 14:44:30 GMT

[Montant cumule] =
var __noContrat = selectedvalue ( T[N°Contrat] )
var __currentDate = max ( T[Date] )
var __cumule =
	SUMX(
		FILTER(
			ALL ( T ),
			T[N°Contrat] = __noContrat
			&&
			T[Date] <= __currentDate
		),
		T[Montant]
	)
return
	__cumule

Re: Cumulative SUM by Month by contrat

Lefuneste57 — Sun, 18 Aug 2019 16:03:41 GMT

It's ok !!!

Thanks for your help ! :smileyvery-happy: