Count periods of employee absence over consecutive days

[lizbethl4]

I would like to count the periods of employee absence > 1 day as 1 occurrence.
If an employee is absent 3 consecutive days, then this should count as 1 occurrence.

employee empID eventdate comments points

[Melissa]

Hi @lizbethl4,

Welcome to the Forum!

It’s not a perfect fit but I think you can leverage of some of the techniques in this thread.

Calculate the number of days the quantity remained constant from a selected date

Ok so here is a solution for you. The heavy lifting is done in Power Query, where I added a column called PrevDate to the Stock movements. Know that this was based on the fact that you had a single Quantity for each Product and Date (if you have multiple transactions, you can use Power Query’s Group by to create this). You can go over the Steps in the Advanced Editor. Did some modelling while I was at it… [image] . And what was left, two simple measures: Quantity by Product = SUM( 'Prod…

If you need further assistance please provide a sample PBIX and a mockup of the expected results.
Thanks!

[jbressan]

Hi @lizbethl4

Please check this post, I think is almost the same logic that you looking for.

Cumulative Hours based in EmpID and ChargeDate

I’m looking to add a calculated column to my below scenario. The basic premise, is I need to 1) Determine how many days in a row an employee worked and 2) Cumulative hours based on the cumulative days. I found a formula for a calculated column for adding the cumulative days on the MS Power BI forum which is working exactly as I need but I’m struggling to accomplish the next step of cumulative hours. [image] I’ve attached a .pbix file with sample data along with the formula for the cumulative…

[EnterpriseDNA]

Hi @lizbethl4, welcome to the Forum! It’s great to know that you are having a great experience so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[lizbethl4]

Hi @jbressan
thank you for your wonderful work. Works great to count the breaks but there are different points assigned for different types of comments and the comments have a unique ID. so if an emp misses a punch or is tardy the point value is .50 while if they have an unscheduled/unexcused, the point value is 1.00. So how to add a condition to this wonderful solution? see how your solution works so far below:

EMP_ID EVENTDATE cummulative break COMMENT Points
222 7/21/2020 0 Unscheduled 1
222 7/22/2020 0 Unscheduled 1
222 7/23/2020 1 Unscheduled 1
222 11/30/2020 1 Missed Punch 0.5
222 3/11/2021 1 Missed Punch 0.5

	TOTAL POINTS SHOULD = 2

[jbressan]

Hi @lizbethl4

For better help, it is recommended to share test data in a pibx file.
Following the logic of the previous post, it will be something like this:

Cumulative Point = 

VAR vCurrentDate = 'Test Data'[Charge Date]
VAR _Ini = 'Test Data'[Cumulative Days Count] - 1
VAR _StartDate = DATEADD('Test Data'[Charge Date],-_Ini,DAY)
VAR vCurrentID = 'Test Data'[Employee ID]

VAR vNextTbl =
    FILTER ( 'Test Data', 'Test Data'[Charge Date] = vCurrentDate + 1 && 'Test Data'[Employee ID] = vCurrentID )
VAR vRangeTbl =
    FILTER ( 'Test Data', 'Test Data'[Charge Date] >= _StartDate && 'Test Data'[Charge Date]<= vCurrentDate  && 'Test Data'[Employee ID] = vCurrentID )

VAR _Acum = CALCULATE(SUM('Test Data'[Points]), ALL('Test Data'),vRangeTbl)
RETURN
    _Acum

[EnterpriseDNA]

Hi @lizbethl4, did the response provided by the users and experts help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

[EnterpriseDNA]

Hi @lizbethl4, we’ve noticed that no response has been received from you since the 6th of April. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

[EnterpriseDNA]

Hi @lizbethl4, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box.

We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

[lizbethl4]

Thank you so much. it worked nicely! you are a genius.

[lizbethl4]

HI jbressan,
it worked fine until there were consecutive dates of different types of comments. the total should be 6 but it counted 5.50 because it thought 5/7 and 5/8 were the same types of occurence.
Please advise and thank you in advance. Also, in your original logic i was not sure about
[cumulative Days Count] so I used the original logic for [cumulative Days Count]. Thank you so much.TIME & ATTENDANCE_SAMPLE_CODE.xlsx (13.7 KB) TIME_ATTENDANCE_OCC_SAMPLE.pbix (43.0 KB)

[lizbethl4]

Hi jbressan,
can you please check for the recent question regarding same problem. i sent it last night.
thanks

[Greg]

Hi @lizbethl4. You’re posting to a solved thread, and, as many forum members only view unsolved issues, your post may not elicit (m)any responses. Please create a new thread for new questions.
Greg

[lizbethl4]

Hi Greg,
how jbressan had sent me the solution but there was an additional issue with it.how
do i connect with him to update the solution?

thanks,

[Greg]

Hi @lizbethl4. This is a community forum, and all issues should remain open to all forum members … singling-out a specific member is discouraged. When a thread has ben marked as solved, please create a new thread and include a much detail as possible (e.g., PBIX, sample data, marked-up screenshot of the desired outcome visual, etc.).
Greg