Analysis of conditions over time with interval periods (week couter - conditional rows SUM)

[HerbertDashwood]

Hi,

I'm working on an analysis on payroll data to check if pensions are paid appropriately for all employees. I'm working with a dataset which shows if an employee has worked in a given week (see attached). The pension fee of an employee is dependent on the 'phase' an employee is in. Based on the amount of weeks an employee has worked a phase can be determined. Even if an employee has only worked 1 hour the whole week counts as a 'worked week'. 

For this analysis I'm tryring to add a column to my table in which the expected phase per week is included based on the following rules:

1. Phase A: 0-26 weeks, no pesions fees
2. Phase B: 26-78 weeks, basis fee
3. Phase C: >78 weeks, plus fee
4. Weeks without hours worked do not count for the phase system
5. If an employee has not worked for over 26 weeks the previous phase is applied (so C becomes B again)

I tried working with a counter which tracks the number op applicable weeks ( this is how I would approach this in say SQL) but as I'm fairly new to PowerBI I did not yet succeed. I'm not sure what the best apporach is here. Especially rule 4 & 5 are a mistery. Does anyone have a suggestion?

Below is a table with dummy data. The column 'Phase' and 'Counter' are the columns I wish to add and now only show the desired results. 

Week-Year	Employee	Worked	Phase	Counter
1-2019	E5590264	Yes	A	1
2-2019	E5590264	Yes	A	2
3-2019	E5590264	Yes	A	3
4-2019	E5590264	Yes	A	4
5-2019	E5590264	Yes	A	5
6-2019	E5590264	Yes	A	6
7-2019	E5590264	Yes	A	7
8-2019	E5590264	Yes	A	8
9-2019	E5590264	Yes	A	9
10-2019	E5590264	Yes	A	10
11-2019	E5590264	Yes	A	11
12-2019	E5590264	Yes	A	12
13-2019	E5590264	Yes	A	13
14-2019	E5590264	Yes	A	14
15-2019	E5590264	Yes	A	15
16-2019	E5590264	Yes	A	16
17-2019	E5590264	Yes	A	17
18-2019	E5590264	Yes	A	18
19-2019	E5590264	Yes	A	19
20-2019			A	19
21-2019			A	19
22-2019	E5590264	Yes	A	20
23-2019	E5590264	Yes	A	21
24-2019	E5590264	Yes	A	22
25-2019	E5590264	Yes	A	23
26-2019	E5590264	Yes	A	24
27-2019	E5590264	Yes	A	25
28-2019	E5590264	Yes	A	26
29-2019			A	26
30-2019	E5590264	Yes	B	27
31-2019	E5590264	Yes	B	28
32-2019	E5590264	Yes	B	29
33-2019	E5590264	Yes	B	30
34-2019	E5590264	Yes	B	31
35-2019	E5590264	Yes	B	32
36-2019	E5590264	Yes	B	33
37-2019	E5590264	Yes	B	34
38-2019	E5590264	Yes	B	35
39-2019	E5590264	Yes	B	36
40-2019	E5590264	Yes	B	37

[Greg_Deckler · Accepted solution]

@HerbertDashwood OK, either in PQ or DAX, get yourself an Index column. In DAX it is:

Index = LEFT([Week-Year],SEARCH("-",[Week-Year])-1)+0

After that the Counter column is a breeze:

Counter = COUNTROWS(FILTER('Table16',[Index]<=EARLIER([Index]) && [Worked]<>""))

Which then makes Phase obvious:

Phase = 
SWITCH(TRUE(),
    [Counter]<=26,"A",
    [Counter]>78,"C",
    "B"
)

[HerbertDashwood]

@Greg_Deckler First of all, many thanks! This is very helpfull. Currently I'm trying to work the last things out.

The dataset I am using is a bit more complex than previously mentioned as it:

1. has data from mulitple years in it (2019 and 2020)
2. Contains all employees
3. Does not record weeks without hours worked

Problem 1 & 3 were fixed by making a few adjustments:

Index =
IF (
    RIGHT ( [WeekYear], 4 ) = "2019",
    LEFT ( [WeekYear], SEARCH ( "-", [WeekYear] ) - 1 ) + 0,
    LEFT ( [WeekYear], SEARCH ( "-", [WeekYear] ) - 1 ) + 53
)

Counter = COUNTROWS(FILTER('CompleteTable',[Index]<=EARLIER([Index])))

Do you have a sugestion on how I could adjust the 'Counter' a bit more to determine the counter for all individual 'Employee' separately included in the total database? I'm now looking into the filtering options.

[Greg_Deckler]

@HerbertDashwood Maybe:

Counter = COUNTROWS(FILTER('CompleteTable',[Employee]=EARLIER([Employee]) && [Index]<=EARLIER([Index])))

[HerbertDashwood]

It is now that I truly begin to understand what EARLIER () does. Code is all fixed. For future references solutions is provded below:

Counter = 
COUNTROWS(
    FILTER(
        'CompleteTable',

// Filter all rows with the EmployeeID of the current row

        EARLIER(CompleteTable[EmployeeID]) = CompleteTable[EmployeeID] 

// Filter all rows <= to the index numer of the current row

        && [Index]<=EARLIER([Index])
        )
)

Thanks again for you help!