Measure to get sum of allocated hours between two dates

[Anonymous]

Hi, 

I have three tables Users, Allocations and Calendar. Most users are allocated 7.5 hours as standard (Monday to Friday), and there can be some users with different hours, and such users have entries in the Allocations table (see below). Day 1 is Monday, Day 2 - Tuesday and so on.

Relationship -> Users 1:1 Allocations

Users:

User Id

101

102

103

104

Allocations

User Id	Day 1	Day 2	Day 3	Day 4	Day 5
102	7.5	3.5	0	7.5	4
104	7.5	7.5	7.5	3	0

Calendar:

Date

Day of Week

Weekday name

I am trying to create a measure to calculate the sum of hours allocated to all users between two dates.

For example, if user_id 101 and 103 have a standard allocation of 7.5 hours on all 5 days, then the Sum of Allocated Hours between Oct 1, 2020 and Oct 9, 2020 for user_ids 101, 102, 103 and 104 would be 167.5. 

Number of weekdays between Oct 1, 2020 and Oct 9, 2020 = 7

Total hours for

User_id 101 = 7.5 * 7 = 52.5

user_id 102 = 7.5 + 4 + 7.5 + 3.5 + 0 + 7.5 + 4 = 34

User_id 103 = 7.5 * 7 = 52.5

User_id 104 = 3 + 0+ 7.5 + 7.5 + 7.5+ 3 + 0 = 28.5

Please can anyone advise how to create this measure. Greatly appreciated. Thanks!

[v-stephen-msft · Accepted solution]

Hi @Anonymous ,

I use unpivot and create a calculated table and a measure.

Dummy Power BI model here.

1.Select these columns and click Unpivot Columns.

2.Create the Calendar table.

Calendar =
ADDCOLUMNS (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2020, 10, 1 ), DATE ( 2020, 10, 9 ) ),
        "Day of Week", WEEKDAY ( [Date], 2 )
    ),
    "Weekday Name", "Day " & [Day of Week]
)

3.Create a new table using DAX.\

Table =
FILTER ( CROSSJOIN ( 'Allocations', 'Calendar' ), [Attribute] = [Weekday Name] )

4.Create the measure to calculate the sum of hours.

Hours =
IF (
    MAX ( 'Users'[User Id] ) IN VALUES ( 'Table'[User Id] ),
    SUM ( 'Table'[Value] ),
    CALCULATE (
        COUNT ( 'Calendar'[Day of Week] ),
        FILTER ( 'Calendar', [Day of Week] IN { 1, 2, 3, 4, 5 } )
    ) * 7.5
)

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nvprasad]

Hi ,

The best solution is, Do the unpivot transformation of the "Allocation" table and create a measure.

Appreciate a Kudos! 🙂
If this helps and resolves the issue, please mark it as a Solution! 🙂

Regards,
N V Durga Prasad