Calculate the number of weeks a task is active

[eloomis]

I have data that looks like this:

Task ID | Task Start Date | Task End Date | Task Budget

123          1/26/23                2/10/23           2

I want to write a measure to calculate how many weeks the task crosses so that I can multiply the task budget by that amount. So the example here the task would cross 3 weeks, so the result would be 6. 

The week starts Monday. Even if the task starts on a Friday, that would still be counted as crossing a full week, so from 1/27 to 1/30 would still be 2 weeks. 

I am including multiple filters for specific task types so the ultimate solution will be more complex, but I'm not sure how to accomplish this piece first. 

Thank you in advance.

[tamerj1 · Accepted solution]

Hi @eloomis 

please try

Total Budget =
SUMX (
Tasks,
(
WEEKNUM ( Tasks[Task End Date], 2 ) - WEEKNUM ( Tasks[Task Start Date], 2 ) + 1
) * Tasks[Task Budget]
)

[eloomis]

Thank you - this is almost perfect.

There is something weird happening where i am getting negative numbers for some. I think it might be in cases where the Start date is in December 2022 and end date is in 2023. Do you have any ideas on how to resolve this?

[tamerj1]

@eloomis 
Please try

Total Budget =
SUMX (
    Tasks,
    VAR StartWeek =
        WEEKNUM ( Tasks[Task Start Date], 2 )
    VAR EndWeek =
        WEEKNUM ( Tasks[Task End Date], 2 )
    VAR NumOfWeeks =
        IF ( StartWeek > EndWeek, 54 - StartWeek + EndWeek, EndWeek - StartWeek + 1 )
    RETURN
        NumOfWeeks * Tasks[Task Budget]
)