Time Sensitive calculations

[Pradeep160]

Hello Power BI Community, 

I have  a table of data as given below. Since the data is given per each week(Assuming week starts from Monday and week has only 5days) there are possibilities when a week starts in one month and ends in another month. 
For example a week starting on 30Dec got 2 working days in December and 3 working days in jan 2025. 
Now I need to show the Utilization by individual month and also by quartwise. How to handle this kind of situation. 
The Utilization formula is given below the data. 

Data Format example: 
Region-- Dept--Week--BHours--NonBHours--ResourceCount
US    --   HR               --  29Dec2024  -- 1314  -- 1622  -- 78
US    --   Admin         --  29Dec2024  --   925  --  20 -- 60
US    --   HR               --  06Jan2025  --  126 -- 24 -- 3
US    --   Admin        --   29Dec2024  --   1023  --  376 -- 30
UK    --   HR               --  29Dec2024  -- 1600  -- 160  -- 88
UK    --   Admin         --  29Dec2024  --   1580  --  21 -- 61
UK    --   HR               --  06Jan2025  --  1260 -- 24 -- 3
UK    --   Admin        --   29Dec2024  --   10231  --  376 -- 30 


Utilization =   BHours / [ (40 * ResourceCount) - NonBHours]
 

[Pradeep160]

Thanks @bhanu_gautam. It worked as per the expectations.

[danextian]

Hi @Pradeep160 

It is unclear whether you want then utilization formula to be evaluated for each date from the week +4 days or BHours, NonBHours and ResourceCount are to be divided by 5 but assuming that the formula is as straigforward as indicated, try this:

Utilization Time Period =
VAR StartDate =
    MIN ( Dates[Date] )
VAR EndDate =
    MAX ( Dates[Date] )
VAR _util =
    SUMX (
        VALUES ( Dates[Date] ),
        SUMX (
            ADDCOLUMNS (
                FILTER ( 'Fact', 'Fact'[Week] <= EndDate && 'Fact'[Week] + 4 >= StartDate ),
                "@Utilization",
                    DIVIDE ( 'Fact'[BHours], ( 40 * 'Fact'[ResourceCount] ) - 'Fact'[NonBHours] )
            ),
            [@Utilization]
        )
    )
RETURN
    _util

Another option, if the data isn't too big, is to materialize each date within the week + 4 in the query editor.

Please see attached sample pbix for details.

[bhanu_gautam · Accepted solution]

@Pradeep160 

Create a new table that expands each week into individual days.

DAX
DailyData =
ADDCOLUMNS (
GENERATE (
'WeeklyData',
CALENDAR (
'WeeklyData'[Week],
'WeeklyData'[Week] + 4
)
),
"Day", [Date],
"DailyBHours", 'WeeklyData'[BHours] / 5,
"DailyNonBHours", 'WeeklyData'[NonBHours] / 5
)

Create new calculated columns to extract the month and quarter from the date.

DAX
DailyData =
ADDCOLUMNS (
DailyData,
"Month", MONTH([Day]),
"Quarter", QUARTER([Day]),
"Year", YEAR([Day])
)

Now, you can create measures to calculate the utilization by month and quarter.

DAX
MonthlyUtilization =
CALCULATE (
DIVIDE (
SUM(DailyData[DailyBHours]),
SUMX (
DailyData,
(40 * DailyData[ResourceCount]) - DailyData[DailyNonBHours]
)
),
VALUES(DailyData[Month]),
VALUES(DailyData[Year])
)

DAX
QuarterlyUtilization =
CALCULATE (
DIVIDE (
SUM(DailyData[DailyBHours]),
SUMX (
DailyData,
(40 * DailyData[ResourceCount]) - DailyData[DailyNonBHours]
)
),
VALUES(DailyData[Quarter]),
VALUES(DailyData[Year])
)