Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Check for Date Before Row Date

Hi,   Is there a way to see if there was an entry from the previous months? So, if there exists a date in a column for an employee before the date in the current row, then count the full number of...
General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Here is an expression you can use in a calculated column that should return your desired results.  Replace Former, Date, and Employee with your actual Table and Column names.

     

    DayCount =
VAR __monthstart =
    EOMONTH ( Former[Date], -1 ) + 1
VAR __monthend =
    EOMONTH ( Former[Date], 0 )
VAR __minthisemploye =
    CALCULATE ( MIN ( Former[Date] ), ALLEXCEPT ( Former, Former[Employee] ) )
RETURN
    IF (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Former ),
                ALLEXCEPT ( Former, Former[Employee] ),
                Former[Date] < __monthstart
            )
        ),
        DATEDIFF ( __minthisemploye, __monthend, DAY ),
        DATEDIFF ( __monthstart, __monthend, DAY ) + 1
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Here is an expression you can use in a calculated column that should return your desired results.  Replace Former, Date, and Employee with your actual Table and Column names.

 

DayCount =
VAR __monthstart =
    EOMONTH ( Former[Date], -1 ) + 1
VAR __monthend =
    EOMONTH ( Former[Date], 0 )
VAR __minthisemploye =
    CALCULATE ( MIN ( Former[Date] ), ALLEXCEPT ( Former, Former[Employee] ) )
RETURN
    IF (
        ISBLANK (
            CALCULATE (
                COUNTROWS ( Former ),
                ALLEXCEPT ( Former, Former[Employee] ),
                Former[Date] < __monthstart
            )
        ),
        DATEDIFF ( __minthisemploye, __monthend, DAY ),
        DATEDIFF ( __monthstart, __monthend, DAY ) + 1
    )

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

thank you! i believe this works 🙂