Check for Date Before Row Date

[Anonymous]

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 days in the month.

For example, if the employee has a current row entry on 4/2/2020, I want to check if they entered anything in January-March.

If they did, then my formula would output 30 since there are 30 days in April. If the employee did not enter anything in January-March, the output would be 30-the first entry in April, so 28.

Please let me know if this question does not make sense...

Thank you!
Sarah

[mahoneypat · Accepted solution]

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]

thank you! i believe this works 🙂

[amitchandak]

@Anonymous , You can use a rolling measure and check

example

Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.