Forum Discussion

Pradeep160's avatar
Pradeep160
Frequent Visitor
1 year ago
Solved

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 we...
Need Help
  • bhanu_gautam's avatar
    bhanu_gautam
    1 year ago

    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])
    )

bhanu_gautam's avatar
bhanu_gautam
Super User
1 year ago

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])
)