Display total hours between two dates where the slicer determines the dates

[Anonymous]

Hello,

New Power BI user who is used to dealing with SQL.  

I have three tables imported in.

1st table is simply a list of values

Releases

v8.3

v8.4

v8.5

v8.6

The second table is just a large calendar of dates (columns important for this issue)

Weekday_num has Sunday = 1,  Monday = 2, etc

Weekday_Ind weekday = 1,  weekend = 0

Hours_Day = Weekday_Ind * 6.5 hours

Example:

Date               Weekday_Num  Weekday_Ind  Hours_Day

01/01/2019                          3                      1               6.5

01/02/2019                          4                      1               6.5

01/03/2019                          5                      1               6.5

01/04/2019                          6                      1               6.5

01/05/2019                          7                      0               0

01/06/2019                          1                      0               0

etc all the way to 2025

The third table is a phase table that links the two (I had the events in columns but unpivoted the data)

Release        Event                     Event_Date        Phase(Calculated column)

v8.3              Analysis_Begin      03/08/2019       Analysis

v8.3              Analysis_End         05/03/2019       Analysis

v8.3              Design_Begin        05/04/2019       Design

v8.3              Design_End           06/07/2019       Design

v8.3              Dev_Begin             06/08/2019       Design

v8.3              Dev_End                07/26/2019       Design

v8.3              ST_Begin               07/27/2019       Design

v8.3              ST_End                  08/23/2019       Design

Users need to be able to select the release and get a summary of available hours per phase per release

Example:

Release               Phase              Available_Hours

v8.3                     Analysis           266.50

v8.3                     Design             162.50

etc.

Any help (different table structure,  measures, calculated columns, etc) is appreciated.

Thanks

[danextian · Accepted solution]

You can do something like these:

Available Hours =
VAR __START =
    CALCULATE (
        MIN ( Table3[Event Date] ),
        ALLEXCEPT ( Table3, Table3[Event], Table3[Release] )
    )
VAR __END =
    CALCULATE (
        MAX ( Table3[Event Date] ),
        ALLEXCEPT ( Table3, Table3[Event], Table3[Release] )
    )
RETURN
    CALCULATE (
        SUM ( Table2[Hours Day] ),
        DATESBETWEEN ( Table2[Date], __START, __END )
    )

Available Hours =
VAR __START =
    CALCULATE (
        MIN ( Table3[Event Date] ),
        ALLEXCEPT ( Table3, Table3[Event], Table3[Release] )
    )
VAR __END =
    CALCULATE (
        MAX ( Table3[Event Date] ),
        ALLEXCEPT ( Table3, Table3[Event], Table3[Release] )
    )
RETURN
    CALCULATE (
        SUM ( Table2[Hours Day] ),
        FILTER ( ALL ( Table2 ), Table2[Date] >= __START && Table2[Date] <= __END )
    )

In the first formula, there is no relationship between your calendar and event tables.