Create column where a date row value = sum of values between start & end date on another table

[Oberon]

Hi,

I'm new to Power BI and trying to figure out how to get the equivilent of an excel sumif referencing dates on another table.

Assuming Table A looked like this example:

Table A

Name	Start Date	End Date	FTE
Joe	1/1/2021	22/12/2023	1
Fred	1/1/2021	3/04/2022	1
Jill	1/1/2021	1/1/2099	1
Kate	1/1/2021	1/1/2099	1

And I had dates in the first column of Table B.

I'm looking to try and get the 2nd column "Sum of FTE" as a calculated column returning:

Sum of table A[FTE] if Table A [Start Date] is less than or equal Table B[Date] and Table A[End Date] is above Table B[Date]

Table B

Date	Sum of FTE
1/04/2022	3
2/04/2022	3
3/04/2022	2
4/04/2022	2
5/04/2022	2
4/04/2022	2

I've tried various combinations of methods but nothing quite gets there. For the most part it tells me I can't reference 2 seperate tables or the value I return from the other table is a single value and not related to the date in Column A of Table B.

[lbendlin]

Please do not use fake end dates. Much better to leave these blank.

Sum of FTE =
VAR a =
    SUMMARIZECOLUMNS ( Dates[Date], Emps[Start Date], Emps[End Date], Emps[FTE] )
VAR b =
    ADDCOLUMNS (
        a,
        "ct",
            COUNTROWS (
                INTERSECT (
                    { [Date] },
                    CALENDAR ( [Start Date], COALESCE ( [End Date], TODAY () ) )
                )
            )
    )
RETURN
    SUMX ( b, [ct] * [FTE] )

see attached

[Oberon]

Thank you Ibendlin,

It's great to see it can be done. I've tried adapting this to my specific data and I get and error saying the start date of the calendar function cannot be later than the end date. (I've done as you mentioned and removed the fake end dates from the sorce data so the values return null).

I suspect my error may have been in providing only historic data in the example when I'm attempting to use it for future dated changes as well. This is what I have at present in case there's any glaring syntax errors in the code I've adapted.

Sum of FTE = 
VAR a = 
    SUMMARIZECOLUMNS ( FTESum[Date], FTEImportData[Start Date], FTEImportData[End Date], FTEImportData[FTE] ) 
VAR b = 
    ADDCOLUMNS ( 
        a, 
        "ct", 
            COUNTROWS ( 
                INTERSECT ( 
                    { [Date] }, 
                    CALENDAR ( [Start date], COALESCE ( [End date],TODAY() ) ) 
                ) 
            ) 
    ) 
RETURN 
    SUMX ( b, [ct] * [FTE] ) 

I've tried playing with changing the formula of Today () to Date and/or adding a coalesce with a historic date to the start date as well but the only other variation of the error I get is that the start or end date can't return null.  Any ideas on where I'm going wrong?

[lbendlin]

when I'm attempting to use it for future dated changes as well.

yes, you may have wanted to mention that.

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[Oberon]

Apologies, rookie mistake. (Litereally my first Power BI attempt).  I'm still adapting my thinking away from Excel where that would have been an easy adaptation for me.

This is a sample of clensed data from the table I'm using.

Agent Name	Employee ID	FTE	Dept	Start date	End date
Name 1	19500001	1	Migrations	1/01/2024
Name 2	19500002	1	Coach	1/01/2024	18/02/2024
Name 3	19500003	1	SME	1/01/2024
Name 4	19500004	1	Consumer	1/01/2024	27/08/2024
Name 5	19500005	1	SME	1/01/2024
Name 6	19500006	1	Consumer	1/01/2024	1/04/2024
Name 7	19500007	1	Consumer	1/01/2024	30/06/2024
Name 8	19500008	1	Premium	1/01/2024
Name 9	19500009	0.6	SME	1/01/2024

The 2nd table remains the same to begin with just cronological dates.

I was looking to tackle the sum of FTE for each date first then work out if I needed a (sum of FTE) column for each department seperately or could just link in the data model and use a slicer so I'll mention that in case it's relevant.

[lbendlin · Accepted solution]