Forum Discussion

htsvhwave's avatar
htsvhwave
Icon for Helper II rankHelper II
3 years ago

Help with creating index column

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

dateType of dayType of day nr
14/04/2022Public holiday3
15/04/2022Weekend2
16/04/2022Weekend2

I have to following tabels

InitialsdateAliasHoursindex(how is want the index to be)
xx10/04/2022sick81
xx11/04/2022sick80
xx12/04/2022sick80
xx13/04/2022sick80
xx17/04/2022sick80
xx18/04/2022sick80
xx19/04/2022work8 
xx20/04/2022sick81
Need Help
Show and Tell

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.