Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
SEunson
Frequent Visitor

Conditional Sum - Two Unrelated Tables

Hello,

 

I have two unrelated tables. In Table A I have a date column and I could like to add a calculated colum which sums up Work Hours in Table B, depending on a condition.

For each row in Table A, I would like to compare the date in Table A and check if it is between two dates in Table B. I would like this to iterate through all the rows in Table B and only sum up the hours where this condition is true.

I tried this, but it doesn't seem to work:

Hours =
    SUMX(
        FILTER(
                   'Table B',
                    TableA[Date] > TableB[Start Date] && TableA[Date] < TableB[Finish Date]
                   ),
        TableB[Work Hours]
)


 Any suggestions or help would be much appreciated.

 

Thanks!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @SEunson 

you may try 

Hours =
VAR CurrentDate = TableA[Date]
RETURN
    SUMX (
        'Table B',
        IF (
            TableB[Start Date] < CurrentDate
                && TableB[Finish Date] > CurrentDate,
            TableB[Work Hours]
        )
    )

View solution in original post

2 REPLIES 2
SEunson
Frequent Visitor

Hi @tamerj1
Thanks for the response. I think this works. I'll just do some testing and will "Accept as Solution" if all looks ok.

tamerj1
Super User
Super User

Hi @SEunson 

you may try 

Hours =
VAR CurrentDate = TableA[Date]
RETURN
    SUMX (
        'Table B',
        IF (
            TableB[Start Date] < CurrentDate
                && TableB[Finish Date] > CurrentDate,
            TableB[Work Hours]
        )
    )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors