Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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
Solved! Go to 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 , 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])))
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,
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
144 | |
73 | |
64 | |
52 | |
51 |
User | Count |
---|---|
208 | |
91 | |
62 | |
59 | |
56 |