Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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.

 
IDMilestone PhaseStart DateEnd DateWorkload
A0-13/30/202012/10/2021200
A1-212/10/202101/03/20222000
A2-301/03/202207/01/20221000
B0-101/02/201905/05/2021150
B1-205/05/202106/06/2022200
B2-306/06/202210/12/2023600

 

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 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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
1.png
Your measure is simply

 

 

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

 

 

Then you can build you report and slice the way you like
1.png
Please let me know if this answers your query. Have a great day!

View solution in original post

3 REPLIES 3
tamerj1
Super User
Super User

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
1.png
Your measure is simply

 

 

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

 

 

Then you can build you report and slice the way you like
1.png
Please let me know if this answers your query. Have a great day!

ValtteriN
Super User
Super User

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/





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.