I could use a bit of help on the following:
I need to calculate provision for stakeholders on the turnover of customers for each day sales were made.
I have a fact table with sales data (i made a simplified version for this topic)
I have a table where I heve a customer name, a stakeholderId, a provision percentage and a DateFrom and DateTo. Since the percentage value may change overtime for a particlar stakeholder, I need to take this into my calculation.
I make use of a datetable (taken from the edna course)
The model is set up like this
I need to retrieve a correct percentage for a sales date based on the total of percentages valid for the various stakeholders on that salesdate.
I created this formula to calculate the valid percentage
The result of this calulation in the final table looks like this
Now, looking at the percentages, it seems that at least part of the calculation went ok.
The question, how do i make it work that these percentages will be connected to the actual salesdata in the table so I can calculate the actual amount valid for that salesdate?
It should look something like this
Note: not every customer that produces sales is valid for provision. In case there is no agreement, the field Percentage should be blank (and the result of the calculation aswell)
I tried several options I learned for the various vid’s, but just can’t get my head around the last part.