Calculate cumulative total value by date and Client

[MagMag31]

Hello,

can you help me please to add calculate column (cumul) that calculate the the cumulative budget total per client per month for each year as explained in the table

thank you

[sergej_og]

Maybe TOTALYTD() can halp in your case

[Ashish_Mathur · Accepted solution]

Hi,

This should ideally be solved with a measure but since you want a calculated column formula solution, write these calculated column formulas

Date = 1*("1/"&Data[Month]&"/"&Data[Year])

RT = CALCULATE(SUM(Data[Budget]),FILTER(Data,Data[Client Number]=EARLIER(Data[Client Number])&&Data[Date]<=EARLIER(Data[Date])))

Hope this helps.

[MINGXIN]

Hi @Ashish_Mathur ,

Hope you are well.

I'm having the same problem, but using measure, not new columns, can you give a little advice? Thanks in advance.

[Ashish_Mathur]

Please use the DATESYTD function within the CALCULATE function.

[MINGXIN]

Hi @Ashish_Mathur ,

Thank you so much for your attention.

I tried the below DAX, but it did not work.

AmountNet_MTD = 
CALCULATE(
sum('Data'[credit_amount])
-sum('Data'[Deb_amount])
,DATESMTD('Data'[Accounting_Date])
)

 I used the Calculate as below, which is ok, but the total is incorrect.

AmountNet_MTD2 = 
 CALCULATE(sum('Data'[credit_amount])-sum('Data'[Deb_amount])
 ,FILTER('Data'
 ,'Data'[Accounting_Date_YYYYMM]=EARLIER('Data'[Accounting_Date_YYYYMM])
 &&'Data'[Accounting_Date]<=EARLIER('Data'[Accounting_Date])
&&'Data'[account_id]<=EARLIER('Data'[account_id])
 &&'Data'[Items_L1]="L1"))

Thanks again.

[Ashish_Mathur]

Hi,

Share some data to work with, explain the question and show the expected result.

[MagMag31]

Thank you so much @Ashish_Mathur it works !!👏

[Ashish_Mathur]

You are welcome.

[Greg_Deckler]

@MagMag31 See if this helps: