cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
vickyd Member
Member

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

Accepted Solutions
Community Support Team
Community Support Team

Re: DAX : Convert Weekly data to daily data

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

1 REPLY 1
Community Support Team
Community Support Team

Re: DAX : Convert Weekly data to daily data

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 Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

Helpful resources

Announcements
October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Badges

Incoming: New and Improved Badges

Exciting news: We've given our badges an overhaul and added brand news ones.

Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 195 members 1,931 guests
Please welcome our newest community members: