How to iterate through rows of a related table and perform a conditional operation?

[Anonymous]

I have 2 tables:

Projects:

PR	StartDate	EndDate	DailyHours
0001	01/01/2022	10/02/2022	6
0002	02/01/2022	02/01/2022	4

Workload:

Date	Workload in hours
01/01/2022	? (should be 6)
02/01/2022	? (should be 10 since 6+4)
03/01/2022	? (should be 6)

How can I calculate the sum of the hours of all the projects that will be active in the specified date? In python code, this would be something like:

foreach date in workload:
    foreach pr in prTable:
        if (myDate is between pr dates):
            dateWorkload += pr[DailyHours]

However, I can't use Python for this purpose. How can I do it in DAX?

[v-jianboli-msft · Accepted solution]

Hi @Anonymous ,

Please try:

Workload in hours = SUMX(FILTER('Projects',[StartDate]<=EARLIER('Workload'[Date])&&[EndDate]>=EARLIER('Workload'[Date])),[DailyHours])

Final output:

Best Regards,

Jianbo Li

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

[Anonymous]

There is a problem with this. It seems to work just fine, but when I use it in a line graphic, filtering by PR won't change the data displayed. They are not directly related, I don't know if that might be the reason.

[amitchandak]

@Anonymous , refer if these can help

Between Dates - Dates between
Tables
https://amitchandak.medium.com/dax-get-all-dates-between-the-start-and-end-date-8f3dac4ff90b
https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Measure way

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Isn't creating a new table with all that data a big performance problem? I have 2000+ projects that can last a few months each.