Forum Discussion

Natrify's avatar
Natrify
Frequent Visitor
2 years ago
Solved

Help needed for counting dates for a calculated column.

Hi all, 

 

I'm working with a large dataset of Employee numbers, dates, and statuses Here is an example of what it looks like (it continues for around a million rows): 

 

 

What I want to do is to make a calculated column with a binary value of '1' or '0'. Where it shows 1 if that particular employee number has NOT had StatusCode NORMAL TIME at any point in the last 6 weeks.

Example: 

 

 

I've taken a stab at it and here is the code that I've got so far, but I'm not sure its correct...

 

 

SixWeekFlag = 
IF (
    CALCULATE (
        COUNTROWS ( Actual ),
        FILTER (
            ALLEXCEPT(Actual, Actual[EmployeeNumber]),
            Actual[StatusCode] = "NORMAL TIME"
        ),
        DATESINPERIOD (
            Actual[Date],
            LASTDATE ( Actual[Date] ),
            -42, ) ) = 0, 1,  0)

 

 

Any ideas? Help is much appreciated.


 

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi lbendlin ,thanks for the quick reply, I'll add further.

    Hi Natrify ,

    The Table data is shown below:

    Please follow these steps:
    1. Use the following DAX expression to create a column

    Column = IF(
    COUNTROWS(FILTER('Table','Table'[Date]< EARLIER('Table'[Date]) && 'Table'[Date] >= EARLIER('Table'[Date]) - 42 && 'Table'[StatysCode] = "NORMAL TIME" &&'Table'[Employeenumber] = EARLIER('Table'[Employeenumber]))) <> 1,
    1,0)

    2.Final output

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

2 Replies