Forum Discussion
Calculate different values based on start and end dates
Hi,
Firstly, I apologise if this has already been posted elsewhere (I've looked but been unable to find a solution to my problem).
I've got the below data set
I need to write a measure that pulls the commission depending on if dates selected fall between the start and end date
(ie. if 08/03/2025 is selected then it pulls in "10" but if 03/03/2025 is selected it pulls in "5")
The idea is to multiply the commission by a number of sales figure (simple sum measure from another table) but I'm falling down when I'm trying to multiply the commission figure by number of sales when it spans different commission figures.
For instance, instead of having (3 sales x "5" on 06/03/2025) + (4 sales x "10" on 07/03/2025) = 55 DAX is summing the commissions and then multiplying it by the number of sales, giving me 105 because it's trying to do 15 (combined 5+10 commission rate) x 7 sales
Apologies again for the convoluted explaination (my first post). It's easier to explain in my head than write out so if any more info is needed, please let me know and I'll do my best to explain!
You can build a summary table which has each date, the number of sales and the commission. You can then do a SUMX over that
Sales commission = VAR SalesWithCommission = ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "@sales", CALCULATE ( SUM ( Sales[Value] ) ), "@commission", VAR CurrentDate = 'Date'[Date] RETURN CALCULATE ( MIN ( 'Commission'[Commission] ), 'Commission'[Start Date] <= CurrentDate && 'Commission'[End Date] >= CurrentDate ) ) VAR Result = SUMX ( SalesWithCommission, [@sales] * [@commission] ) RETURN Result
2 Replies
johnt75Super User
You can build a summary table which has each date, the number of sales and the commission. You can then do a SUMX over that
Sales commission = VAR SalesWithCommission = ADDCOLUMNS ( VALUES ( 'Date'[Date] ), "@sales", CALCULATE ( SUM ( Sales[Value] ) ), "@commission", VAR CurrentDate = 'Date'[Date] RETURN CALCULATE ( MIN ( 'Commission'[Commission] ), 'Commission'[Start Date] <= CurrentDate && 'Commission'[End Date] >= CurrentDate ) ) VAR Result = SUMX ( SalesWithCommission, [@sales] * [@commission] ) RETURN Result