Hello,
I am working with contracts that have a total value and an end date. I want to spread the total value on each working day between today and the end date.
So far I calculated the daily value of each contract, but I am stuck when it comes to calculating each day`s value (all contracts together).
The contract table looks basically like this:
customer no., contract no., end date, total value
1, 123, 31.08.2019, 20k
2, 456, 31.12.2020, 50k
3, 556, 30.09.2019, 5k
1, 448, 31.01.2020, 10k
Each total value should be spread evenly among the days between today() and the end date.
e.g. 20k shall be spread 1,67k (20k/12days) for each day from 20.08. to 31.08.2019
5k shall be spread 0,12k (5k/42days) for each day from 20.08. to 30.09.2019
Finally I want to have the summed daily value available.
I am working with a time table and I am working with a working days table where each working day is marked with 1 (and 0 for non-working days). I am using current offset for working days.
Calculating the daily value of each contract I have done with this formula:
ExpWD Rev = if('offene Kontrakte_BI'[Gültig bis] > today(); CALCULATE(SUM('offene Kontrakte_BI'[Contracts EUR]))/CALCULATE(SUM('Calendar_working days_BI'[CurWDoffset]));0)
Thank you for your help!
