PowerBI Model time dependant

Hi lbendlin 

that's the way the information is coming from the source system. The assumption is that the recording is done in working days within business hours. For the report we don't care.

for me the main problem is how I can do the model to know the workingschedule valid for the day the hours were recorded. Thank you!

Follow up, to show more details of how exactly I transformed your original tables into the model(s) I shared. I did all the transformation/construction in Power Query. It would be possible to do all this in DAX, too, but then you'll have duplicated data loaded into your model.

Here is all the M for the seven tables above.

Calendars

let
    Source = WorkingSchedule,
    SelectCols = Table.SelectColumns(
        Source, {"WorkingSchedule", "  MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
    ),
    Distinct = Table.Distinct(SelectCols, {"WorkingSchedule"}),
    AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
    MoveIdFirst = Table.ReorderColumns(
        AddId, {"ID", "WorkingSchedule", "  MoHrs", "Wk.hrs.", "Hrs/Day", "WkDys", "AnnualHrs"}
    )
in
    MoveIdFirst

Dates

let
    Source = RecordedHours[Recording Date],
    MinDate = List.Min(Source),
    MaxDate = List.Max(Source),
    Dates = List.Dates(
        Date.StartOfYear(MinDate),
        Duration.Days(Date.EndOfYear(MaxDate) - Date.StartOfYear(MinDate)) + 1,
        #duration(1, 0, 0, 0)
    ),
    ToTable = Table.FromColumns(
        {Dates}, 
        Type.AddTableKey(type table [Date = date], {"Date"}, true)
    ),
    AddYear = Table.AddColumn(ToTable, "Year", each Date.Year([Date]), Int64.Type),
    AddQuarter = Table.AddColumn(AddYear, "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    AddMonthNo = Table.AddColumn(AddQuarter, "MonthNo", each Date.Month([Date]), Int64.Type),
    AddMonth = Table.AddColumn(AddMonthNo, "Month", each Date.MonthName([Date]), type text),
    AddYearMonthNo = Table.AddColumn(
        AddMonth, 
        "YearMonthNo", 
        each [Year] * 100 + [MonthNo], 
        Int64.Type
    ),
    AddYearMonth = Table.AddColumn(
        AddYearMonthNo, 
        "YearMonth", 
        each Text.From([Year]) & "-" & Text.Start([Month], 3), 
        type text
    )
in
    AddYearMonth

Technicians

let
    Source = TechnicianDetails,
    SelectTechCols = Table.SelectColumns(Source,{"Technician", "Technician name"}),
    DistinctTech = Table.Distinct(SelectTechCols, {"Technician"}),
    AddId = Table.AddIndexColumn(DistinctTech, "ID", 1, 1, Int64.Type),
    MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Technician", "Technician name"})
in
    MoveIdFirst

OrgPositions

let
    Source = TechnicianDetails,
    SelectOrgCols = Table.SelectColumns(Source,{"Organization", "JobPosition", "CostArea"}),
    Distinct = Table.Distinct(SelectOrgCols),
    AddId = Table.AddIndexColumn(Distinct, "ID", 1, 1, Int64.Type),
    MoveIdFirst = Table.ReorderColumns(AddId,{"ID", "Organization", "JobPosition", "CostArea"})
in
    MoveIdFirst

TechCalDates

let
    Source = WorkingSchedule,
    MergeTechs = Table.NestedJoin(
        Source, {"Technician"}, 
        Technicians, {"Technician"}, 
        "Technicians", JoinKind.Inner
    ),
    MergeCals = Table.NestedJoin(
        MergeTechs, {"WorkingSchedule"}, 
        Calendars, {"WorkingSchedule"}, 
        "Calendars", JoinKind.Inner
    ),
    MaxDate = List.Max(Dates[Date]),
    AddDates = Table.AddColumn(
        MergeCals,
        "Dates",
        each List.Dates(
            [Start Date], 
            Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1, 
            #duration(1, 0, 0, 0)
        ),
        type {date}
    ),
    SelectCols = Table.SelectColumns(AddDates, {"Technicians", "Calendars", "Dates"}),
    ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
    ExpandCalID = Table.ExpandTableColumn(ExpandTechID, "Calendars", {"ID"}, {"CalID"}),
    ExpandDates = Table.ExpandListColumn(ExpandCalID, "Dates")
in
    ExpandDates

TechHours

let
    Source = RecordedHours,
    MergeTechs = Table.NestedJoin(
        Source, {"Technician"}, 
        Technicians, {"Technician"}, 
        "Technicians", JoinKind.Inner
    ),
    SelectCols = Table.SelectColumns(
        MergeTechs, 
        {"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
    ),
    ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"})
in
    ExpandTechID

TechOrgDates

let
    Source = TechnicianDetails,
    MergeTechs = Table.NestedJoin(
        Source, {"Technician"}, 
        Technicians, {"Technician"}, 
        "Technicians", JoinKind.Inner
        ),
    MergeOrgPos = Table.NestedJoin(
        MergeTechs,
        {"Organization", "JobPosition", "CostArea"},
        OrgPositions,
        {"Organization", "JobPosition", "CostArea"},
        "OrgPositions",
        JoinKind.Inner
    ),
    MaxDate = List.Max(Dates[Date]),
    AddDates = Table.AddColumn(
        MergeOrgPos,
        "Dates",
        each List.Dates(
            [Start Date], 
            Duration.Days(List.Min({MaxDate, [End Date]}) - [Start Date]) + 1, 
            #duration(1, 0, 0, 0)
        ),
        type {date}
    ),
    SelectCols = Table.SelectColumns(AddDates, {"Technicians", "OrgPositions", "Dates"}),
    ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
    ExpandOrgPosID = Table.ExpandTableColumn(ExpandTechID, "OrgPositions", {"ID"}, {"OrgPosID"}),
    ExpandDates = Table.ExpandListColumn(ExpandOrgPosID, "Dates")
in
    ExpandDates

If your business rules allow for the simplified model I described (no need to support M:M), then you can disable the load on the two bridge tables, TechCalDates & TechOrgDates, and use the below in TechHours to move the foreign keys over. (Note there are probably more efficient ways to get the FKs here without first building out the bridge tables):

let
    Source = RecordedHours,
    MergeTechs = Table.NestedJoin(
        Source, {"Technician"}, 
        Technicians, {"Technician"}, 
        "Technicians", JoinKind.Inner
    ),
    SelectCols = Table.SelectColumns(
        MergeTechs, 
        {"Technicians", "Recording Date", "Hours", "HourType", "project", "Registered on"}
    ),
    ExpandTechID = Table.ExpandTableColumn(SelectCols, "Technicians", {"ID"}, {"TechID"}),
    MergeCals = Table.NestedJoin(
        ExpandTechID, {"TechID", "Recording Date"}, 
        TechCalDates, {"TechID", "Dates"}, 
        "TechCalDates", JoinKind.Inner
    ),
    ExpandCalID = Table.ExpandTableColumn(MergeCals, "TechCalDates", {"CalID"}, {"CalID"}),
    MergeOrgs = Table.NestedJoin(
        ExpandCalID, {"TechID", "Recording Date"}, 
        TechOrgDates, {"TechID", "Dates"}, 
        "TechOrgDates", JoinKind.Inner
    ),
    ExpandOrgID = Table.ExpandTableColumn(MergeOrgs, "TechOrgDates", {"OrgPosID"}, {"OrgPosID"})
in
    ExpandOrgID