Return true if filtered date is between 2 date columns

[Kevin086]

Hi all, I'm building a report for my internship at a Health and Safety service in the Netherlands. 

Columns to know

I have a Date column with the first day of illness;

I have a Date column with the recovery date.

I have a column that has the value 1 if a person is Ill for 9Months+ and a 0 if he/she isnt;

What I need

I want to create a graph that sums the amount of people which are on that moment 9months+ sick -> per date.

At this moment it looks like this: 

As you can see the graph drops. This is because for ex. someone who got sick in 2019, but is still sick in 2020, will only be summed in 2019. This is bacouse it counts the first day of illness. -> I want this person to be summed in 2019, as well as 2020. Until this person is recovered.

In short, I would like to show the running files per date. Not the created files per date

Idea so far

After a day of work, this is what I ended up with. I think I need a measure which returns +1 for each row when the Illness first day is before the max filtered date and the recovery date is after the min filtered date and 9 months + = 1.

I just can't get the DAX formula which could acquire this... Does someone know if this idea solves the problem or has a better solution? And could anyone provide me with the DAX formula of this- or another solution?

Any help is highly appreciated. Thank you in advance!

[MattAllington]

Yes you will need a measure. In this case, you should not use a relationship between your calendar table and data table.  Relationships are designed to filter your data, hence you will only ever get the year it started. I don't have you data, but this is how I would approach the problem.

1. Go to the data view

2. Find the date columns and use the filters at the top of these columns

3. Filter the data table so you can see everyone that was off at least 1 day in a random year, say 2019. You will work it out because you will break the problem down into logical steps. Just filter everyone that started their leave before 31 Dec 2019 and finished their leave (or is still on leave) after 1 Jan 2019. 
4. This is the candidate list. Then you have to work out how many days they were away in that year. You can subtract the start date from the end date (or start of year from end of year).

once you have the logic, write it down on paper, then work out the DAX formula. 

if your stuck and you can provide some realistic sample data, I may do a video on this one for my YouTube channel and share it back here. 

hope that helps. 

[Kevin086]

Hi Matt,

Thank you for your reply.

I get that the date table, because of the relation with firstdayillness, is filtering on first day illness. However, I do need a slicer where you can filter on a period. How can i accomplish this without the relationship?

I already have the daysSick column calculated, that is the column i calculated the 9 months+ with. I just don't know the DAX-formula on how to solve this issue. Also the year thing is an example, if someone filters on a month or even a day, it should return the sum of ongoing files for that Year/Month/Day. I probably need something like:

Measure = 

Var Amount = 0 

if (Illness first day is before the max filtered date and the recovery date is after the min filtered date and 9 months+ = 1, Amount +1, Amount +0)

Could you provide me with the correct DAX formula on how to accomplish this? Thank you.

[MattAllington]

I can show you.  Can you post some realistic sample data with a good cross section of sickness across years, within years, etc

[Kevin086]

Sure here's the link for the sample. It contains random data from 2016 untill now. 

 https://www.dropbox.com/scl/fi/9qtyclu9ln2dnudoff5l4/Sample-date.xlsx?dl=0&rlkey=1bbmbecgacloh8na69p...

[MattAllington · Accepted solution]

Here is the explanation. Sorry about the Audio quality. I had a mic problem.  

https://youtu.be/UgZMLR0uJG0

[Kevin086]

Thank you so much Matt, it works!

[MattAllington]

One thing to note. The approach does not differentiate which year do they tip into 9 months. Eg, if someone became sick on 30 Dec 2019 and then went 13 months before returning, they would show up in 2019, 2020 and 2021. This may not be exactly what you want - it depends