Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Be one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now

Reply
RemiAnthonise
Helper V
Helper V

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
LivioLanzo
Solution Sage
Solution Sage

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!  

View solution in original post

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

View solution in original post

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

View solution in original post

8 REPLIES 8
LivioLanzo
Solution Sage
Solution Sage

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!  

Hi @LivioLanzo , thanks a lot!

I'll apply this to my model.

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

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

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?

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

Thanks again! My report is finished now.

@LivioLanzo

Thanks a lot, you're awesome!

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Dec Fabric Community Survey

We want your feedback!

Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.