cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

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

Proud to be a Datanaut!

Solution Sage

@RemiAnthonise

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

Proud to be a Datanaut!

Solution Sage

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

Proud to be a Datanaut!

8 REPLIES 8
Solution Sage

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

Proud to be a Datanaut!

Helper V

Hi @LivioLanzo , thanks a lot!

I'll apply this to my model.

Helper V

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?

Solution Sage

@RemiAnthonise

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

Proud to be a Datanaut!

Helper V

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?

Solution Sage

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

Proud to be a Datanaut!

Helper V

Thanks again! My report is finished now.

Helper V

@LivioLanzo

Thanks a lot, you're awesome!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors