Count of Occurences and divide

[soumyaiyer]

@speedramps 

Hi, I have a list of agents and the date when he/she called in sick. Here is what I am trying to achieve.

1 occurence = 3 continous days.

I would need to number of occurences for each agent and by supervisor for the last 12 months. As we log new entries I would the number of occurences to show or limit to last 12 months. 

[v-karpurapud]

Hi @soumyaiyer 

Thank you for contacting the Microsoft Fabric Community Forum.
 

Create a date table in the Power BI model to support time-based filtering, such as capturing the last 12 months. In Power Query, sort the data by agent and date, then add an index column within each agent group. Create a new column by subtracting the index from the date; this helps group consecutive days, as the result remains the same for continuous sequences.

Next, group the data by agent and this new column to identify each streak of consecutive days. Count the number of days in each group and define occurrences based on this count, one occurrence for up to three days and one occurrence per every three days for longer streaks. Include agent, occurrence date, supervisor and occurrence count in the resulting table.

Load this grouped data into Power BI and create a DAX measure using DATESINPERIOD to filter for the last 12 months dynamically. Use this in a visual such as a matrix or table to display the number of occurrences per agent and supervisor.

If this does not meet your requirements, please provide more details about your issue, including any sample data or the pbix file. This information will help us assist you more effectively.

Regards,
Karpurapu D,
Microsoft Fabric Community Support Team.

[soumyaiyer]

Thank you! I will work on using the above suggestion and revert whether I can was able to achieve the desired results. 

[speedramps]

Question:-

Do 4 continous days counts as 1 or 2 occurences?

Do 5 continous days counts as 1 or 2 or 3 occurences ?
Do 6 continous days counts as 1 or 2 or 3 or 4 occurences ?
Do 7 continous days counts as 1 or 2 or 3 or 4 or 5 occurences ?

and so on

[soumyaiyer]

Here is the udpate:

4 continous days counts as 1

 5 continous days counts as 1 
 6 continous days counts as 1 
7 continous days counts as 1 & so on 

[speedramps · Accepted solution]

Hi again @soumyaiyer 

Many thanks for confirming that 3 continous days or more is just one occurance.

Here is a solution, it is rather complicated, so hopefully you will learn lots of new dax ....

Firstly create a Sickdays table with who was sick when

Create an Agents table  with supervisors

Create a Calendar table

Calendar = CALENDARAUTO()

Create relationships

Create DAX measures

IsSick = 
// return 1 if agent was off sick for the day in question
INT(NOT(ISEMPTY(Sickdays)))

IsNotSick = 
// return 1 if agent was not off sick for the day in question
INT(ISEMPTY(Sickdays))

First day sick = 
// get the cursor date
VAR mydate = MIN('Calendar'[Date])

// create a temp table of dates before the agent was sick
VAR datesbefore =
FILTER(
    ALL('Calendar'[Date]),
    'Calendar'[Date] < mydate &&  [IsNotSick] = 1
)

// get the last date from the temp table
VAR datebefore =
CALCULATE(
MAX('Calendar'[Date]),
datesbefore
)

RETURN
// get the first day off sick
datebefore + 1

Last day sick = 
// get the cursor date
VAR mydate  = MIN('Calendar'[Date])

// create a temp table of dates after the agent was sick
VAR datesafter =
FILTER(
    ALL('Calendar'[Date]),
    'Calendar'[Date] > mydate &&  [IsNotSick] = 1
)

// get the first date from the temp table
var dateafter =
DATEVALUE(
CALCULATE(
MIN('Calendar'[Date]),
datesafter
)
)
RETURN
// get the last day off sick
dateafter - 1

Duration sick = 
// get the duration off sick
DATEDIFF(
    DATEVALUE([First day sick]),
    DATEVALUE([Last day sick]),
    DAY
)

Occurence = 
// get the date cursor
var mydate = MIN('Calendar'[Date])

RETURN
// anything over 3 continuous days is just one occurence

IF(mydate = [First day sick] &&
  [Duration sick] >= 3,
  1)

Occurences = 
// user SUMX interator function to repeat the occurence calculation
// for all dates within all agents.
// Note the Calendar SUMX needs a CALCULATE wrapper to force the interation each time.

SUMX(Agents,
CALCULATE(
SUMX('Calendar',[Occurence])
))

Test results

Click here to download my PBIX from Onedrive

Click here 

Please click thumbs up because I have spent a long time helping you.

Then click [accept solution] if it works.

Many thanks for quoting @speedramps in your original question.
I felt flattered you aksed me to help, thank you.

It was a good problem !

Other Superusers may suggest alternative solutions which calculate the same answer a different ways.
I like this solution because you can see an audit trail of how totals are caculated.

[soumyaiyer]

Hi @speedramps 

Thank you so much for the quick response and I really apprceciate your help here. I will go ahead and use the solution advised. One quick question, need to track the occurences rolling 12 months. Would you recommend to add slicer for the year and month so the end user can select the months and year. 

Once again thank you very much. I will keep posted once I complete this report. I do need help with another report. I am unable to upload the file my organization has restrictions. I will share the snap shot and summarize and quote your name ! 

[speedramps]

Hi again @soumyaiyer 

Many thanks for accept the solution and your kind words.

I am always happy to help when I am available but chose to only provide one answer per question.

If a member provides a wrong or incomplete question, then I polite ask that they raise a new ticket.
I ask this because it keeps ticket simple, spreads the workload and I fairly get points for each a answer.

Take care with a 12 month silicer.

The DAX I provided contains  ALL('Calendar'[Date])  which does what it says on the tin and will overide a slicer.
You will may need to replace it with ALLSELECTED which does use the slicer

Also you need to consider 

if agent Fred had last 2 days this 12 months and the first day of the next 12 months 
and agent Julie had last 3 days this 12 months and the first 3 day of the next 12 months 

According to your rules both has one occurence of 3 of more continuous days,

but if you implement the date slicer incorrectly then Fred will have no occurence  and 
Julie will have two occurence (one in each rolling year) !

See if you can fathom it yourself.

I like to teach members DAX rather than try do their jobs for them.
If you give someone a fish then you just give then a meal,
but if you teach them to fish then they can feed themselves and teach others.

Feeding the world with knowledge 😀

[soumyaiyer]

checking on the HR policy for this , will revert once I have the update.

[soumyaiyer]

I have the list of supervisors in the data set will add them to the slicer since I need the occurence at the agent level. Thanks!

[ronrsnfld]

And, as I requested, are you going to show what you expect for results?

[ronrsnfld]

You don't show a column with the supervisors. And you don't show what you want for results. Please clarify.