Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 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(
    mCalendar[IsBusinessDay]),
    FILTER(mCalendar,
    mCalendar[Dates] >= Customer[DataStartDate] &&
    mCalendar[Dates] <= Customer[CompletedDate]
                         )
    )

    sorry it is so late, hope it helps

    ------------------------------
    larry pfaff
    Sr Business Analyst
    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
    Longmeadow MA
    8602807221
    ------------------------------