Filter report on date: slicer for date range

[RemiAnthonise]

Hi guys,

I'm working on a report which shows me when our employees have been sick and for how long. The absence has a startdate and an enddate. It must be very simple but for some reason I don't get it working.

I have a slicer based on startdate. If I pick the option 'between' I can fill in my daterange. If someone is sick from 1-1-2018 till 31-01-2018 I see this row if I set my startdate on 1-1-2018, but not if I set it on 2-1-2018. This makes sense. What I want to have is an option to see who / how many employees were sick on a certain date. So I have to get a range from startdate - enddate for each employee. If 3 employees are sick from 1-1-2018 till 31-1-2018 and I pick the date 10-1-2018 I want to see the 3 corresponding employees.

I've tried changing my slicer, not basing it on startdate but I made a datetable but this doesn't work. I hope this is clear. I've attached a pbix with testdata, this will explain a lot.

I hope you guys can help me, thanks!

https://www.dropbox.com/s/hdmend8n6sec7a8/Sick%20employees%20test.pbix?dl=0 

[LivioLanzo · Accepted solution]

Hi @RemiAnthonise

this implies slightly changing your model and then the measure to use is very simple.

I have attached a file you can download here: https://1drv.ms/u/s!AiiWkkwHZChHjyZ6yGc-JbOjqEeh

[LivioLanzo · Accepted solution]

@RemiAnthonise

check out the new file: https://1drv.ms/u/s!AiiWkkwHZChHjyZ6yGc-JbOjqEeh

[LivioLanzo · Accepted solution]

Hi @RemiAnthonise

this implies slightly changing your model and then the measure to use is very simple.

I have attached a file you can download here: https://1drv.ms/u/s!AiiWkkwHZChHjyZ6yGc-JbOjqEeh

[RemiAnthonise]

Hi @LivioLanzo , thanks a lot!

I'll apply this to my model.

[RemiAnthonise]

@LivioLanzo 

I'm applying this to my model. It works but I'm missing the startdate and the enddate from the Absences-table. I also want to show these dates but I can't create the relationship between the Absences-table and the SickEmployees you've created.

Can you help me?

[LivioLanzo · Accepted solution]

@RemiAnthonise

check out the new file: https://1drv.ms/u/s!AiiWkkwHZChHjyZ6yGc-JbOjqEeh

[RemiAnthonise]

Hi @LivioLanzo ,

I still got a little question: I want to change the EndDate if it's 1-1-1900, I want to leave it blank. Normally I do this like this:

EndDate 2=
IF([EndDate] = DATE(1900;1;1); BLANK(); ([EndDate]))

This doesn't work. I think because of the table you've created. Do you know how to solve this?

[LivioLanzo · Accepted solution]

@RemiAnthonise

you can change the query that generates the table to: 

SickEmployees =
SELECTCOLUMNS (
    GENERATE (
        SUMMARIZE (
            Absences,
            Absences[HcmWorker_FK_PersonnelNumber],
            Absences[StartDate],
            Absences[EndDate]
        ),
        VAR Strt =
            INT ( Absences[StartDate] )
        VAR EndDte =
            IF (
                INT ( Absences[EndDate] ) = DATE ( 1900, 1, 1 ),
                TODAY (),
                INT ( Absences[EndDate] )
            )
        RETURN
            CALENDAR ( Strt, EndDte )
    ),
    "HcmWorker_FK_PersonnelNumber", [HcmWorker_FK_PersonnelNumber],
    "StartDate", [StartDate],
    "EndDate", IF ( INT ( [EndDate] ) = DATE ( 1900, 1, 1 ), BLANK (), [EndDate] ),
    "Date", [Date]
)

[RemiAnthonise]

Thanks again! My report is finished now.

[RemiAnthonise]

@LivioLanzo

Thanks a lot, you're awesome!