DAX Calculate the sum of values for a year with start date and end date



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



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


Log in to reply
 


Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2