Cumulative measure during only one year

[Anonymous]

Hello, 

I am trying to calculate the effort accumulation for 2022 per each department. I have the following tables

• Projects: A table with all the projets and the effort per department and the starting and finishing date. Like this format (in project 1 department A and B are included and in project 2 only department B)

Project	Department	Effort/department-day	Starting Date	Finish Date
1	A	50	11/12/2021	03/02/2022
1	B	70	11/12/2021	03/02/2022
2	B	30	25/09/2022	03/02/2023
3	A	60	10/02/2022	10/05/2022

• Calendar: A calendar for with all the dates and a column called "laboral" which can be 0 (the day is holiday or weekend) or 1 (the day is a working day)

The tables are not related between them

For the effort/date without accumulation I have used the following formula, if it is helpful

Effort Measure Department = 

var Wrong= IF(ISBLANK('Projects'[Starting Date])),True,False)

var Effort=SUMX(FILTER (
            'Projects',
            'Projects'[Starting Date] <= MAX('Calendar'[Date])
              && 'Projects'[Finish Date] >= MIN('Calendar'[Date])),
              
            'Projects'[Effort department-day]*SUMX (
                                FILTER (
                                    'Calendar',
                                    'Calendar'[Date] >= MIN ( 'Projects'[Starting Date] )
                                        && 'Calendar'[Date] <= MAX ('Projects'[Finish Date])
                                ),
                                'Calendar'[Laboral]
             ))

return IF(Effort=0||Wrong,0,Effort)

But I am not able to convert this formula in order to show the cumulative effort per 2022. Do you have any idea about how to perform this? 

I have tried this formula but the working days per each project are always the same value, and not calculated individually

Effort Measure 2022 = 

var Wrong= IF(ISBLANK(('Projects'[Starting Date])),True,False)

var Effort=SUMX(FILTER (
            'Projects',
            'Projects'[Starting Date] <= MAX('Calendar'[Date])           
              ),          
            'Projects'[Effort department-day]*
                CALCULATE(COUNTROWS(ALL('Calendar')),FILTER('Calendar','Calendar'[Laboral]=1 ),
                DATESBETWEEN('Calendar'[Date],MAX(DATE(2022,1,1),RELATED('Projects'[Starting Date])),
                MIN(date(2023,1,1),Related('Projects'[Finish Date]))
                )
                ))
                            
return IF(Effort=0||Wrong,0,Effort)

Thanks, 
Pablo

EDIT: Update with expected outcome graph

[ManguilibeKAO]

Hi,

You have given the input. Could you show us the output you want to have, given this input?

Best regards.

[Anonymous]

Hi, 

Hope this graph helps