Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
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:

 

 

Pedro_Okazaki_0-1625603144746.png

 

 

 

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
amitchandak
Super User
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])))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

2 REPLIES 2
amitchandak
Super User
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])))

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

@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,

 

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.