The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
thank you! i believe this works 🙂
@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.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
97 | |
80 | |
62 | |
57 |
User | Count |
---|---|
246 | |
119 | |
114 | |
86 | |
70 |