Cumulative Total for the year

[Anonymous]

Hello All, 

I need an assistance with cumulative total since I ve tried many times but it is not working. Attached the matrix:

This table contains the running total gross profit for a salesrep. The cumulative total I need is the Cumulative of NEW GP(USD)

For information ,

the new GP(USD) is equal to growth Year To Date minus Growth Previous Year to Date

The Growth Year to Date is equal to the sum of all net revenue gained by the salesrep. (calculate(sum(Sales_Data[Net Revenue]),(Sales_Data[Salesrep] = the name of the salesrep in question).

The previous year growth is the above formula but with the sameperiodlastyear.

I have a calendar table called Transaction Date Calendar and is related to the transaction date in my Sales Table.

The formula I am using is 

CALCULATE([New GP (USD)],FILTER(ALL(Sales_Data),Sales_Data[Transaction Date]<=MAX('Transaction Date Calendar'[Date]) && MAX(Sales_Data[TransactionYear])))

Would anybody know the correct way to calculate it?

[amitchandak · Accepted solution]

@Anonymous , Datesytd should help you , with help from the date table joined with Transaction Date

YTD Sales = CALCULATE([New GP (USD)],DATESYTD('Date'[Date],"12/31"))

or

CALCULATE([New GP (USD)],FILTER(ALL(Sales_Data),Sales_Data[Transaction Date]<=MAX('Sales_Data'[Date]) && Sales_Data[TransactionYear] = MAX(Sales_Data[TransactionYear])))

[Anonymous]

@amitchandak ,

Both worked perfectly, thank you ver much sir.

Just a question, if you could answer me it would be helpful:

CALCULATE([New GP (USD)],FILTER(ALL(Sales_Data),Sales_Data[Transaction Date]<=MAX('Sales_Data'[Date]) && Sales_Data[TransactionYear] = MAX(Sales_Data[TransactionYear])))

Why should I use here Sales Data[Transaction date] instead of Transaction Date Calendar'[Date]?

Thank you again,