How to set up a calculated column to write back a date from another row and count during these dates

[seppel123]

Hi all,

i am working on a problem in Power BI and could not solve it.

I have a table with Dates, Names of Employees and Status of these employees. Also, there is a Column with the previous Status of that employee and the NextStatus. My raw data and the 2 columns i wanted looks like this:

Date	Name	Status	PrivStatus	NextStatus	K start	K end	K end (wanted)	Amount K during K start and end (wanted)
29. Jan 24	Employee1	A	A	A
01. Feb 24	Employee1	A	A	A
02. Feb 24	Employee1	A	A	K
05. Feb 24	Employee1	K	A	K	05.02.2024		17.02.2024	6
06. Feb 24	Employee1	K	K	K
09. Feb 24	Employee1	K	K	K
10. Feb 24	Employee1	K	K	K
13. Feb 24	Employee1	K	K	K
14. Feb 24	Employee1	K	K	A
17. Feb 24	Employee1	A	K	A		17.02.2024
18. Feb 24	Employee1	A	A	A
21. Feb 24	Employee1	A	A	A
28. Mrz 24	Employee1	A	A	A
29. Mrz 24	Employee1	A	A	A
01. Apr 24	Employee1	A	A	A
02. Apr 24	Employee1	A	A	A
05. Apr 24	Employee1	A	A	A
06. Apr 24	Employee1	A	A	K
09. Apr 24	Employee1	K	A	K	09.04.2024		11.05.2024	6
10. Apr 24	Employee1	K	K	K
03. Mai 24	Employee1	K	K	K
04. Mai 24	Employee1	K	K	K
07. Mai 24	Employee1	K	K	K
08. Mai 24	Employee1	K	K	A
11. Mai 24	Employee1	A	K	A		11.05.2024
12. Mai 24	Employee1	A	A	A
15. Mai 24	Employee1	A	A	U
16. Mai 24	Employee1	U	A	U
09. Jan 24	Employee2	A	A	K
10. Jan 24	Employee2	K	A	K	10.01.2024		17.01.2024	3
13. Jan 24	Employee2	K	K	K
14. Jan 24	Employee2	K	K	A
17. Jan 24	Employee2	A	K	A		17.01.2024
18. Jan 24	Employee2	A	A	U
26. Jan 24	Employee2	U	A	A
29. Jan 24	Employee2	A	U	A
30. Jan 24	Employee2	A	A	K
02. Feb 24	Employee2	K	A	K	02.02.2024		18.02.2024	4
03. Feb 24	Employee2	K	K	K
14. Feb 24	Employee2	K	K	K
15. Feb 24	Employee2	K	K	A
18. Feb 24	Employee2	A	K	A		18.02.2024
19. Feb 24	Employee2	A	A	K

I managed via calculated column to generate the column "K start": 

K Start= 
VAR Date_K_Start = 
    IF(
        AND([Status] = "K", [PrivStatus] <> "K"),
        [Date],
        BLANK()
    )
RETURN
    FORMAT(Date_K_Start, "DD.MM.YYYY")

The same i used to get the column K end:

K end = 
VAR Date_K_end= 
    IF(
        AND([Status] <> "K", [PrivStatus] = "K"),
        [Date],
        BLANK()
    )
RETURN
    FORMAT(Date_K_end, "DD.MM.YYYY")

But of course K end goes in the wrong row in the Column. My question is how to i get it in the right row (together with the K start). Since there are many Employees in this list, it needs to filter correctly for the same name.

Furthermore: I would like to count how many times "K" in [Status] appears during K start and K end. 

Thanks in advance! 

seppel123

[danextian · Accepted solution]

Hi @seppel123 

Please see the attached pbix.