cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
maarten_74 Frequent Visitor
Frequent Visitor

Create a new table with a row for each weekday between two dates for each ID

Hi there,

 

I've got a hour-related table that looks like figure 1. i would like to convert this table so it looks like figure 3 or figure 4. 

Figure 1:

1.png

 

For each workingday between the begindate and enddate there a line created with an equal distubution of the hours based on working days.

Figure 2:

2.png

Figure 3:                                           Figure 4:

3.png         4.png

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Create a new table with a row for each weekday between two dates for each ID

Hi @maarten_74

Create calculated tables. For the one in Figure2: 

NewTable2 =
GENERATE (
    ALL ( Table1[ID] );
    VAR AuxTable_ =
        FILTER (
            CALENDAR (
                CALCULATE ( VALUES ( Table1[Begindate] ) );
                CALCULATE ( VALUES ( Table1[Enddate] ) )
            );
            NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 }
        )
    RETURN
        ADDCOLUMNS (
            AuxTable_;
            "Hours"; CALCULATE ( DISTINCT ( Table1[Hours] ) ) / COUNTROWS ( AuxTable_ )
        )
)

and the one in Figure3:

NewTable3 =
GENERATE (
    ALL ( Table1[ID] );
    VAR AuxTable_ =
        CALENDAR (
            CALCULATE ( VALUES ( Table1[Begindate] ) );
            CALCULATE ( VALUES ( Table1[Enddate] ) )
        )
    VAR NumWorkingDays_ =
        COUNTROWS ( FILTER ( AuxTable_; NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 } ) )
    RETURN
        ADDCOLUMNS (
            AuxTable_;
            "Hours"; IF (
                NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 };
                CALCULATE ( DISTINCT ( Table1[Hours] ) ) / NumWorkingDays_;
                0
            )
        )
)
1 REPLY 1
Super User
Super User

Re: Create a new table with a row for each weekday between two dates for each ID

Hi @maarten_74

Create calculated tables. For the one in Figure2: 

NewTable2 =
GENERATE (
    ALL ( Table1[ID] );
    VAR AuxTable_ =
        FILTER (
            CALENDAR (
                CALCULATE ( VALUES ( Table1[Begindate] ) );
                CALCULATE ( VALUES ( Table1[Enddate] ) )
            );
            NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 }
        )
    RETURN
        ADDCOLUMNS (
            AuxTable_;
            "Hours"; CALCULATE ( DISTINCT ( Table1[Hours] ) ) / COUNTROWS ( AuxTable_ )
        )
)

and the one in Figure3:

NewTable3 =
GENERATE (
    ALL ( Table1[ID] );
    VAR AuxTable_ =
        CALENDAR (
            CALCULATE ( VALUES ( Table1[Begindate] ) );
            CALCULATE ( VALUES ( Table1[Enddate] ) )
        )
    VAR NumWorkingDays_ =
        COUNTROWS ( FILTER ( AuxTable_; NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 } ) )
    RETURN
        ADDCOLUMNS (
            AuxTable_;
            "Hours"; IF (
                NOT WEEKDAY ( [Date]; 2 ) IN { 6; 7 };
                CALCULATE ( DISTINCT ( Table1[Hours] ) ) / NumWorkingDays_;
                0
            )
        )
)

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 177 members 2,380 guests
Please welcome our newest community members: