Forum Discussion

RemiAnthonise's avatar
8 years ago
Solved

Filter report on date: slicer for date range

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's avatar
    LivioLanzo
    8 years ago

    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]
    )

8 Replies