Bonus calculation cumulative amount

DAX
1

Hallo

attache a PBIX
all seems to work but I can get the cumul of the bonus to work
I use the window()

any help is very appreciated

kind regard

roger

bonus calc
bonus calc736×341 14.9 KB

test leveranciers bonus.pbix (169.9 KB)

2

Is there anyone who can help me with this problem of the running total of the cumulative bonus ?

3

Dear @Roger ,

Answer:
A solution is using the default dates from the Datumtabel, instead of month-nr, whereby it becomes possible to use the standard YTD and date-add functions.

For the correct context for the standard time-intelligence measure-formulas, Datumtabel.Date has to be used (in the rows of the table), which could be aggregated to months.

image

However I am still struggling to present the totals for all the Leveranciers and total of the monthly bonus…

PBIX attached:
test leveranciers bonus (2).pbix (172.7 KB)

Cumulative YTD-Bonus is calculated on cumulative YTD sales x bonus %, monthly bonus is derived as a result of cumulative YTD bonus -/- cumulative YTD bonus of previous month.

Measures: (all can be found in the measure group “Add. measures”)

Cum YTD Sales II = CALCULATE(TOTALYTD([Gross Sales],DATUMTABEL[Date]))

Prev. Cum YTD Bonus II = if(MAX(DATUMTABEL[Date].[MonthNo]) = 1, BLANK(),
                            CALCULATE([Cum. YTD Bonus II],DATEADD(DATUMTABEL[Date],-1,MONTH)))

Table with cumulative bonus: ( totals per Leverancier and for all Leveranciers must still be worked out)

image
image929×336 30.4 KB

I hope it helps you further,

Kind regards,
Jan van der Wind

4

Jan

thank you very much for the solution and the explanation how to get there

kind regards

Roger

5

Goedenavond @Roger,

To let your Bonus-measure work for multiple Leveranciers, it should be :

var LEVerancier = SELECTEDVALUE(LEVERANCIERS[ID])

“Selectedvalue” instead of “Values”

Kind regards, Jan van der Wind