User specified date range for total days

[Jimboblue]

I have built a schedule in pbi where I have roughly 1500 seperate tasks. All tasks have a start date and finish date. I would like to know how many days of each task fall within a user specified date range. For example; one task starts on 01/01/2024 and ends on 01/05/2024. If the user specifies a date range of 01/01/2024 - 10/01/2024, it would return 5 days. Similarily, if a task starts on 01/01/2024 and ends on 31/01/2024 and the user specifies 25/12/2023 - 05/01/2024 then it will return 12 days.

The context is that I need the accumulative days for a variety of reporting periods. I have a different table with costs per day for a variety of different types of tasks. If I can get the number of days for the reporting period, then I can multiply this by the cost per day.

[v-tangjie-msft · Accepted solution]

Hi @Jimboblue ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a table.

user specifies = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

(3) We can create a measure. 

Measure = 
var _a=IF(MAX('Table'[start date])>=MIN('user specifies'[Date]),MAX('Table'[start date]),MIN('user specifies'[Date]))
var _b=IF(MAX('Table'[end date])<=MAX('user specifies'[Date]),MAX('Table'[end date]),MAX('user specifies'[Date]))
return DATEDIFF(_a,_b,DAY)+1

(4) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.