Can Window Functions Do The Heavy Lifting in the HR Reports?

Anna, the HR lady from a previous story, recently learnt that Power BI DAX was extended with the so-called window functions that could help her simplify the way she calculated core HR metrics and what’s important, improve performance of her calculations.

The idea behind those Windows, Offset and Index functions is that they go over a specified sorted and partitioned table and pick up the values of relative or absolute rows, computing on the fly their sums, running totals and moving averages.?

Sounds fantastic, no? However, Anna is a bit intimidated by those window functions and their syntaxis. I can totally understand her, I still stumble with lots of bits and pieces as I continue exploring these functions. Anyway, let’s get Anna going and show her a couple of simple examples how she could effectively start using window functions in her HR reports. ??

Case 1:?OFFSET: GO UP AND DOWN THE LADDER

The easiest way to start with OFFSET function is to calculate the traditional Last Year, Last Month measures.

Ingredients:

Leavers –a measure Sum(Leavers)

OFFSET- your navigation system that will take you ?up or down the ladder of a specific dimension column. -1 -movement direction and number of steps to be taken. -1-go back to the previous row ?and get data from there, +5 go forward (down the table) 5 rows etc.

ALLSELECTED/ALL- specifies a table/column to navigate over and obtain values, perform your calculations.

ORDERBY- sorts the dimension column that is used for your calculation.

Whats worth mentioning, if you wish your data change when filters coming from the specified column/table are applied, then use ALLSELECTED, when not then ALL. The difference is quite obvious from the following two snapshots.

If you are using OFFSET on a text column, make sure that you include that column into your formula. In the case below we don’t want the months to be sorted alphabetically, we want them to be sorted by month number, but all what we get is an empty outcome.

Lets fix it and add MonthNumber or MonthAge or Month if it is a whole number to ORDERBY section.

At some point Anna thought she could already slice and dice data like a pro with the OFFSET Function and she decided to use data hierarchies in her visuals. Little did she know that hierarchies with OFFSET function still require a bit of a workaround.?So, let us place Year and MonthName onto the matrix, and what we can see is that the month level was calculated quite fine as opposed to the year level where we can see the sum of all values in the column per year rather than the yearly shift of the values (the total of previous year)- which can be a bit confusing.

Here we need to start using parameters and calculate year and month offsets separately, then use ISINSCOPE function to reach the result we want.?

Leavers LM=

var MonthOffset=

Calculate([Leavers], ( OFFSET(

??-1, ALLSELECTED('

Calendar'[Year],

'Calendar'[Month], '

Calendar'[MonthNumber]),

ORDERBY(

'Calendar'[Year], ASC,'

Calendar'[MonthNumber], ASC))))

?var YearOffset=

Calculate([Leaver], ( OFFSET(

??-1, ALLSELECTED(

'Calendar'[Year]),

ORDERBY('Calendar'[Year], ASC))))

?RETURN

If(NOT(ISINSCOPE('Calendar'[Month])), YearOffset, MonthOffset)

If you want to use an inbuilt data hierarchy, then you have to adjust your formula as follows:

Leavers LM =

var MonthOffset=

Calculate([Leavers], ( OFFSET(

??-1, ALLSELECTED(

'Calendar'[Date].[Year],

'Calendar'[Date].[Month],

'Calendar'[Date].[MonthNo]), ORDERBY('Calendar'[Date].[Year], ASC,'Calendar'[Date].[MonthNo], ASC))))

??

var YearOffset= Calculate([Austritte], ( OFFSET(

??-1, ALLSELECTED(

'Calendar'[Date].[Year]),

ORDERBY('Calendar'[Date].[Jahr], ASC))))

RETURN

If(NOT(ISINSCOPE('Calendar'[Date].[Month])), YearOffset, MonthOffset)

From here on Anna can start calculating the differences between the previous months and years and see the %growth of her leavers, joiners.

Case 2: WINDOW: PARTITIONING

Phew..finally Anna can move to the next requirement of hers.?Anna has a table of individual employees, their respective departments and total hours of sick leave taken. She could potentially use Matrix or a bar chart to see the totals and average sick hours per department, but to calculate the individual percentages of each employee towards the department, to create the color formatting rules with the averages would start making DAX trickier and trickier,?

so Anna decides in favour of Window function that can help her easily partition her data by department.?

Ingredients:

TotalSickLeave–a measure Sum(SickLeaveHours)

WINDOW- creates a span of rows in each partition to navigate over

1,ABS- first row in the partition

-1 ABS -last row in the partition.

In this case we calculate a window between the first row and last row of a partition and we define those rows with the help of absolute values: 1,ABS, -1,ABS.

SUMMARIZE(ALLSELECTED(Table)-creates a table to go over.

PARTITIONBY-specifies the column to calculate over =per

Now Anna can calculate the individual percentages of employees from the total of departamental hours.

From here on, Anna can simply copy paste and adjust her formulas to get Average Sick Leave Hours per Department, Average Sick Leave across all the departments, etc.

Lets remove partition by and calculate average across all departments:

Anna gradually starts getting the hang of using Power BI window functions but there are still areas that need to be actively played with and thoroughly tested out. The example with date hierarchies highlighted some of the weaknesses window functions might have. Next time lets have a closer look at the WINDOW function and how it can be implemented in MAT Calculations, which are the core of all HR Reports. On top of that, Josh, our restaurant owner, has been reallz struggling with a requirement where window functions could possibly help out. Lets see if Josh can share some positive news about his Power BI struggles next time:)

Are you already actively using this new set of DAX Window functions in your reports?