March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe 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
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
Solved! Go to Solution.
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!
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!
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]
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
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!
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?
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?
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]
)
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Thanks again! My report is finished now.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
84 | |
82 | |
74 | |
49 |
User | Count |
---|---|
160 | |
144 | |
103 | |
74 | |
57 |