0

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.

example

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).

Gabriel Valvin
  • 63
  • 8

1 Answers1

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 = 
SUMX(
    FactWorkload,
    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.

GregGalloway
  • 1,385
  • 6
  • 10