topic Re: DAX Calculate the sum of values for a year with start date and end date in DAX Commands and Tips

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

Anonymous — Tue, 22 Feb 2022 08:28:25 GMT

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

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

ValtteriN — Tue, 22 Feb 2022 08:50:50 GMT

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/

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

Anonymous — Tue, 22 Feb 2022 09:26:57 GMT

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

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

tamerj1 — Tue, 22 Feb 2022 10:06:40 GMT

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!