Count how many months within a year an employee took sick leave

[MaggieWang]

Hi everyone,

In this Employee table, column is a list of ID, columns B and C state the sickness start and end dates. I would like to write a DAX measure that counts how many months the individual took sick leave. If someone had multiple occurrences in the same month, I want to count this person only once, but if the sickness occurred in different month, I want to count this person multiple times, once for each month the sickness occurred. So for example if sickness took place in 3 different months, I want to could this person 3 times.

The result I would like to achieve is like the following but is achieved with a DAX measure. I have a Date table listing every day in 2020, the Date table is linked to the Employee table by Sickness Start Date column.

Thank you for your help

With best wishes

Maggie

[MaggieWang · Accepted solution]

I have worked out the following DAX measure which has solved my problem. I am adding it here in case someone else encounters similar issues to mine.

Number Of Occurrence =

VAR Occurrence = SUMMARIZE(Employee, 'Date'[Month])

VAR Result = SUMX(Occurrence,1)

RETURN Result

[amitchandak]

@MaggieWang , refer to my blog or the file attached after signature if they can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[MaggieWang]

@amitchandak  thank you. I actually had found your blog post from this community while searching for a solution to my current problem. Unfortunately this is not going to help me with my problem. At the momehnt I am not too concerned about the length of individual's sickness, your solution would help if I was. What I am looking for is to use Sickness Start Date to access how frequently (i.e. how many months) an employee took sick leave within given period, say a year.  If an employee had multiple occurrences in the same month, I only want to count this person once, not each occurrence. However if the same person took sick leave in different month, I want to count this person multiple times, once for each month the sickness occurred. Thanks.