# Power BI Exchange

View Only

## Sum values between dates from another table.

• #### 1.  Sum values between dates from another table.

Posted Mar 30, 2021 11:56 AM
Hello,

I have two tables,

Table A:

JobID | DateFrom | DateTo | Hrs_per_day
J1 | 1/Jan/21 | 31/Jan/21 | 5,5
J2 | 4/Jan/21 | 2/Feb/21 | 3.6
J3 | 26/Feb/21 | 9/Mar/21 | 4.2
....
(the hrs_per day field is calculated dividing a given number of hours for each job by number of days between start and end)

And Table B which is a Date table, each row a single a day:

Date
1/Jan/21
2/Jan/21
3/Jan/21
...

I need to add a column to table B, which for each row reports the sum of hrs_per_day of all jobs in Table A which are "active" for the corresponding Table B day.
in pseudo formula
mycolumn=sum (hrs_per_day)  if   date>=DateFrom && date<=DateTo

I've find and tried many DAX formula I found online to get this result but it looks like my example is never exactly addressed, or at least I didnt get it to work.
most of what i have seen is something like.
mycolumn=sumx(calculate(sum(hrs_per_day), filter (Table A, date>=min(FromDate) && date<=max(ToDate)))

This formula doesnt work, i use an excel file to crosscheck the numbers. What I don't get is if and why I have to set a relationship bewtween Table A and  Table B, I see no logical links between the tw. ,
I would prefer so see the result as a calculated column in the date table (Table B) ,but if a measure is the only way I can cope.

Any clue much appreciated!

Andrea

------------------------------
Andrea Pusceddu
Technical Director
Cagliari
------------------------------

• #### 2.  RE: Sum values between dates from another table.

Posted Dec 03, 2021 09:04 AM
Edited by larry pfaff Dec 03, 2021 09:26 AM
I have solved a similar issue. I have calculated "BusinessDays" not including weekends and holidays, between dates with a measure. My line item has a start and end date, I have a relationship between my table and calendar. my formula returns a 1 or 0 based on if it is a business day. then I sum that column.
CALCULATE(
SUM(
FILTER(mCalendar,
mCalendar[Dates] >= Customer[DataStartDate] &&
mCalendar[Dates] <= Customer[CompletedDate]
)
)

sorry it is so late, hope it helps

------------------------------
larry pfaff
jacksonville FL
9048745451
------------------------------

• #### 3.  RE: Sum values between dates from another table.

Top Contributor
Posted Dec 17, 2021 09:05 AM
Hi Andrea:

Your question would be much easier to solve if you had the daily details for each job ID by date as your Fact table. E.G.,

JOB ID    Hours    Date
1                4         1-1-21
1                6         1-2-21
1                2          1-3-21
2               10         1-1-21
2                5          1-2-21
etc
Just a thought.

------------------------------
William Skelley
01106