I can't manage in DAX, to calculate for a year, the sum of the values which are in this year, I would like the proportion of each of the values in a year according to its starting date and its ending date.


For example, I want to be able to calculate for each year, the value of the "workload" during that year, so in 2019 the share of 150 between 01/02/2019 and the end of the year then the share of 150 between 01/01/2020 and 12/31/2020 then the share between 01/01/2021 and 05/05/2021 (without forgetting the phase 1-2 and the other IDs obviously).

1 Answer 1


Please call the table you listed in your example FactWorkload.

Create a new table called DimDate with one row per day at least spanning the date ranges mentioned in FactWorkload if not more. Add a Year column to that table. Do not create a relationship between DimDate and FactWorkload.

Create a new DAX measure:

Prorated Workload = 
    VAR DaysSelected = CALCULATE(COUNTROWS(DimDate),DimDate[Date] >= EARLIER(FactWorkload[Start Date]) && DimDate[Date] < EARLIER(FactWorkload[End Date]))
    VAR TotalWorkloadDays = DATEDIFF(FactWorkload[Start Date],FactWorkload[End Date],DAY)
    RETURN DIVIDE(FactWorkload[Workload] * DaysSelected, TotalWorkloadDays)

The results with DimDate[Year] on rows and FactWorkload[ID] on columns I get looks like the following. If I've understood your calculations, I believe that's correctly dividing the Workload amount across years.

results of DAX calc

For example the 89.35 (2020 A) is calculated 277 * 200 / 620 = 89.35. That's 277 days in the year 2020 * 200 workload value for A phase 0-1 / 620 days between 3/30/2020 and 12/10/2021 not counting the end date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.