cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sk15227
Post Patron
Post Patron

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 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

1 ACCEPTED SOLUTION
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Super User
Super User

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

thank you! i believe this works 🙂

 

 

amitchandak
Super User
Super User

@sk15227 , 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-Y...


Appreciate your Kudos.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors