Forum Discussion
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
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
3 Replies
- mahoneypat
Microsoft Employee
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
- amitchandak
Super User
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.
