cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Cumulative Total for the year

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?

1 ACCEPTED SOLUTION
Super User

@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])))

2 REPLIES 2
Super User

@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
Not applicable

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,

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.