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

[Anonymous]

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.

ID	Milestone Phase	Start Date	End Date	Workload
A	0-1	3/30/2020	12/10/2021	200
A	1-2	12/10/2021	01/03/2022	2000
A	2-3	01/03/2022	07/01/2022	1000
B	0-1	01/02/2019	05/05/2021	150
B	1-2	05/05/2021	06/06/2022	200
B	2-3	06/06/2022	10/12/2023	600

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

[tamerj1 · Accepted solution]

Hi @Anonymous 
This is a typical Decompose / Reassemble puzzle. Here is your sample file with the solution https://www.dropbox.com/t/UstztAstnn94u8hK
You need to create a new table at the "Day" granularity. The following code calculates this table with daily workload.

Workload Per Day = 
VAR PerDayWorkload =
    ADDCOLUMNS (
        Workload,
        "Workload/Day", DIVIDE ( Workload[Workload], DATEDIFF ( Workload[Start Date], Workload[End Date], DAY ) )
    )
VAR DailyTable =
    CROSSJOIN ('Date', PerDayWorkload )
VAR Result = 
    FILTER (
        DailyTable,
        [Date] >= [Start Date]
            && [Date] <= [End Date]
    )
RETURN
    Result

Simply use the new table to build a relationship with the Date table

Your measure is simply

Workload Measure = SUM ('Workload Per Day'[Workload/Day] )

Then you can build you report and slice the way you like

Please let me know if this answers your query. Have a great day!

[ValtteriN]

Hi,

You can achive this by removing filters from date using ALL and then use variables to get values for a set year. E.g.

Yearly = var _year = YEAR(Table[StartDate]) return
CALCULATE(SUM(table[workload]),ALL(Table[StartDate]),YEAR(Table[StartDate])=_year)


After you have this yearly value you can calculate the row context dependand value by using a simple SUM calculation and then use DIVIDE(SUM(table[workload]),[Yearly])

Ping me with @ if I have misunderstood something or you have questions.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Anonymous]

Hello @ValtteriN , thank you very much for this answer,
after Yearly = var _year = YEAR(Table[StartDate])
StartDate is not a measure I can not put this value in parameter I do not understand why