Re: Help with creating index column

htsvhwave · ‎07-11-2023

Currenly i am trying to make a index column to calculate absence periods for employees. With the current calculation the period end when there is a weekend or a public holiday, where as i need i to continue. when i period start where the alias is sick i need the index to be 1, after that the index should be zero until the period ends. If the alias isnt sick the index should be empty

I have my date tabel

date	Type of day	Type of day nr
14/04/2022	Public holiday	3
15/04/2022	Weekend	2
16/04/2022	Weekend	2

I have to following tabels

Initials	date	Alias	Hours	index(how is want the index to be)
xx	10/04/2022	sick	8	1
xx	11/04/2022	sick	8	0
xx	12/04/2022	sick	8	0
xx	13/04/2022	sick	8	0
xx	17/04/2022	sick	8	0
xx	18/04/2022	sick	8	0
xx	19/04/2022	work	8
xx	20/04/2022	sick	8	1

Anonymous · ‎07-17-2023

Hi @htsvhwave ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create calculated columns.

Column = VAR CurrentDate = 'Table'[date]
VAR PreviousDate = CALCULATE(MAX('Table'[date]), FILTER('Table', 'Table'[date] < CurrentDate))
RETURN IF(WEEKDAY(CurrentDate, 2) > 1, PreviousDate, CALCULATE(MAX('Table'[date]), FILTER('Table', 'Table'[date] < CurrentDate - 1)))

Index = 
VAR PreviousAlias =CALCULATE(MAX('Table'[Alias]),FILTER('Table','Table'[date]=EARLIER('Table'[Column])))
RETURN
    IF (
        'Table'[Alias] = "sick",
        IF (
            PreviousAlias = "sick",
            0,
            1
        ),
        BLANK ()
    )

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.

htsvhwave · ‎07-24-2023

Hi

i have updated the sample date with other scenrios. When a absence period start i need the index to be 1 and ather that 0. I have some employees that can have of days during the week and kan work during the public holiday and weekends somtimes so i hope the calculation also could considere this.