I need to calculate a prorate value with dax
I have 2 tables:
calendar table : contains dates and days worked
| date | days worked |
|---|---|
| 01/04/2024 | 0 |
| 02/04/2024 | 1 |
| 03/04/2024 | 1 |
| 04/04/2024 | 1 |
| 05/04/2024 | 1 |
| 06/04/2024 | 0 |
| 07/04/2024 | 0 |
| 08/04/2024 | 1 |
| 09/04/2024 | 1 |
| 10/04/2024 | 1 |
| 11/04/2024 | 1 |
For april in the month of April we have 21 working days
objective table: contains the objective values by date.
| obj date | obj |
|---|---|
| 05/04/2024 | 200 |
| 06/04/2024 | 150 |
| 07/04/2024 | 50 |
| 010/04/2024 | 300 |
| 012/04/2024 | 788 |
| 015/04/2024 | 450 |
| 016/04/2024 | 325 |
| 018/04/2024 | 6510 |
| 19/04/2024 | 20 |
| 22/04/2024 | 241 |
| 24/04/2024 | 178 |
I need to calculate the prorate of the current month:
divide ((sum(obj) , sum(days worked))*sum(days worked from the first day of the month to today)
for this exemple :
- sum(obj) = 6212
- sum(days worked) in the month = 21
- sum(days worked) from first day to today = 20
divide (6212 , 21)*20 = 5916,19