Forum Discussion
Time Sensitive calculations
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]
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])
)
3 Replies
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])
)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 _utilAnother 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.
Thanks bhanu_gautam. It worked as per the expectations.
