cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

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 

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Filter report on date: slicer for date range

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: Filter report on date: slicer for date range

@RemiAnthonise

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Super User
Super User

Re: Filter report on date: slicer for date range

@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 ( 190011 ),
                TODAY (),
                INT ( Absences[EndDate] )
            )
        RETURN
            CALENDAR ( StrtEndDte )
    ),
    "HcmWorker_FK_PersonnelNumber", [HcmWorker_FK_PersonnelNumber],
    "StartDate", [StartDate],
    "EndDate"IF ( INT ( [EndDate] ) = DATE ( 190011 )BLANK (), [EndDate] ),
    "Date", [Date]
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

8 REPLIES 8
Super User
Super User

Re: Filter report on date: slicer for date range

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

 

 

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: Filter report on date: slicer for date range

Hi @LivioLanzo , thanks a lot!

I'll apply this to my model.

Re: Filter report on date: slicer for date range

@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?

Super User
Super User

Re: Filter report on date: slicer for date range

@RemiAnthonise

 

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

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: Filter report on date: slicer for date range

@LivioLanzo

Thanks a lot, you're awesome!

Re: Filter report on date: slicer for date range

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?

Super User
Super User

Re: Filter report on date: slicer for date range

@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 ( 190011 ),
                TODAY (),
                INT ( Absences[EndDate] )
            )
        RETURN
            CALENDAR ( StrtEndDte )
    ),
    "HcmWorker_FK_PersonnelNumber", [HcmWorker_FK_PersonnelNumber],
    "StartDate", [StartDate],
    "EndDate"IF ( INT ( [EndDate] ) = DATE ( 190011 )BLANK (), [EndDate] ),
    "Date", [Date]
)

 


 


Did I answer your question correctly? Mark my answer as a solution!


Proud to be a Datanaut!  

Re: Filter report on date: slicer for date range

Thanks again! My report is finished now.