DAX : Convert Weekly data to daily data

[vickyd]

I have what I believe is a fairly common scenario related to timesheets that I've been breaking my head on.

Scenario: Each employee is available for X numbers of hours on a weekly basis. Typically it is 40 hrs per week but could be different. It would also be lesser if say an employee exits or joins the company in the middle of the week. This data is stored in a table. 

Our reporting is at a Monthly (calendar month) level so I need a way to calculate the Available Hours by month for a given employee. Ideally, would like it to be flexible so it can calculate Available Hours for any period chosen i.e. One could choose 2 months or 6 weeks or 24 days.. basically any custom start and end date and the measure should be able to tell exactly how many hours the employee was available during that period. 

I don't necessarily want to extrapolate and store the data if there is a way to achieve what I really need using just a DAX formula i.e. a measure that just takes the weekly data and is able to provide the available hours for any selected period. 

What's the best solution? 

Green box represents the data I have and Blue box represents the extrapolated data.

[v-shex-msft · Accepted solution]

HI @vickyd,

You can refer to below sample to generate the analysis daily work hour table.

1. Merge targe tables.

Merged = UNION(Table1,Table2) 

2. Generate the calendar table with id and target.

Result =
VAR startDate =
    MINX ( Merged, [End Date] )
        + ( 1 - WEEKDAY ( MINX ( Merged, [End Date] ), 1 ) )
VAR endDate =
    MAXX ( Merged, [End Date] )
RETURN
    ADDCOLUMNS (
        CROSSJOIN (
            CALENDAR ( startDate, endDate ),
            DISTINCT ( SELECTCOLUMNS ( Merged, "ID", [ID] ) )
        ),
        "Target", LOOKUPVALUE (
            Merged[Work Hour],
            Merged[ID], [ID],
            Merged[End Date], [Date]
                + ( 7 - WEEKDAY ( [Date], 1 ) )
        )
            + 0,
        "Day of Week", FORMAT ( [Date], "ddd" ),
        "Month", FORMAT ( [Date], "mmm" ),
        "Work Hour", IF ( WEEKDAY ( [Date], 1 ) <> 1 && WEEKDAY ( [Date], 1 ) <> 7, 8, 0 )
    )

3. Add calculated column to calculate the available work hour.

Available Hours =
VAR isWorkDay =
    IF (
        WEEKDAY ( [Date], 1 ) <> 1
            && WEEKDAY ( [Date], 1 ) <> 7,
        TRUE (),
        FALSE ()
    )
VAR rolling =
    SUMX (
        FILTER (
            ALL ( Result ),
            [ID] = EARLIER ( [ID] )
                && WEEKNUM ( [Date], 1 ) = WEEKNUM ( EARLIER ( Result[Date] ), 1 )
                && [Date] < EARLIER ( [Date] )
        ),
        [Work Hour]
    )
RETURN
    IF (
        [Target] = 0,
        0,
        IF (
            rolling < [Target]
                && rolling + 8
                > [Target],
            [Target] - rolling,
            IF ( rolling < [Target] && isWorkDay, 8 )
                + 0
        )
    )

Regards,

Xiaoxin Sheng