cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Lefuneste57 Frequent Visitor
Frequent Visitor

Cumulative SUM by Month by contrat

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 ?

 

 

SnipImage.JPG

 

 

 

 

 

 

 

 

Thanks for helping.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Cumulative SUM by Month by contrat

[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
6 REPLIES 6
Super User
Super User

Re: Cumulative SUM by Month by contrat

[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
Lefuneste57 Frequent Visitor
Frequent Visitor

Re: Cumulative SUM by Month by contrat

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 :

SnipImage.JPG

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

But with your formule, i have 260 :

SnipImage.JPG

 

Thank you very much for your help.

Super User
Super User

Re: Cumulative SUM by Month by contrat

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

Best
Darek
Lefuneste57 Frequent Visitor
Frequent Visitor

Re: Cumulative SUM by Month by contrat

I'm sorry.

 

Can you help me ?

 

Super User
Super User

Re: Cumulative SUM by Month by contrat

[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
Lefuneste57 Frequent Visitor
Frequent Visitor

Re: Cumulative SUM by Month by contrat

It's ok !!!

 

Thanks for your help !  Smiley Very Happy

 

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 283 members 2,797 guests
Please welcome our newest community members: