Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vickyd
Helper V
Helper V

DAX : Convert Weekly data to daily data

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.

 

Time.png

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

HI @vickyd,

 

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

 

1. Merge targe tables.

Merged = UNION(Table1,Table2) 

8.PNG

 

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 )
    )

9.PNG

 

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
        )
    )

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

1 REPLY 1
v-shex-msft
Community Support
Community Support

HI @vickyd,

 

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

 

1. Merge targe tables.

Merged = UNION(Table1,Table2) 

8.PNG

 

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 )
    )

9.PNG

 

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
        )
    )

10.PNG

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.