Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I have a table containing absences of employees including a start and an end date.
I created a date table having an active relationship between the date from the data table and the start date of the absence. There is also an inactive relationship between the date of the date table and the end date of the absence.
I would like to know the number of people absent on a specific day but I don't manage to find the correct DAX syntax.
For the number of people being absent on the start date, the syntax I am using is:
As you can see, I created integers based on the dates and used these integers to build the relationships.
Can anyone provide a solution on how to calculate the number of people being absent on a given day (meaning where the day is equal to or bigger than the start date and equal to or smaller than the end date)?
Thank you,
Jos
Solved! Go to Solution.
Hi, @JVL
Create a new Table
Date = CALENDAR(MIN('Table'[Begindate]),MAX('Table'[Enddate]))
Create two measures, the following is DAX
Measure = IF(MAX([Begindate])<= MAX('Date'[Date]) && MAX([Enddate]) >= MAX('Date'[Date]),1)
Sum Example = COUNTROWS(FILTER(ALLSELECTED('Table'),[Begindate]<= MAX('Date'[Date]) && [Enddate] >= MAX('Date'[Date])))
Here is my preview
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @JVL
This is my train of thought, I create a table that assumes that all the dates are absent dates.
And then creates a new date table based on the smallest date and the largest date.
Table 2 = CALENDAR(MIN('Table'[DateAsInteger]),MAX('Table'[DateAsIntegerEnd]))
This is my model relationship.
Then create a measure to give a logo to the absentees.
Logo =
CALCULATE (
COUNTAX (
FILTER (
'Table',
MIN ( 'Table'[DateAsInteger] ) <= MIN ( 'Table 2'[Date] )
&& MIN ( 'Table 2'[Date] ) <= MAX ( 'Table'[DateAsIntegerEnd] )
|| MAX ( 'Table 2'[Date] ) >= MAX ( 'Table'[DateAsInteger] )
&& MAX ( 'Table 2'[Date] ) <= MAX ( 'Table'[DateAsIntegerEnd] )
),
( 'Table'[ID] )
),
CROSSFILTER ( 'Table'[DateAsInteger], 'Table 2'[Date], NONE )
)
Create a measure to count the total number of absences over a period of time, and use a slicer to see the number of absences over a period of time
sum = COUNTROWS('Table')
Here is my preview
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data)
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the feedback @v-yohua-msft but this is not giving me the expected result.
I added some sample data below:
Absence_ID Begindate Enddate
1 01/01/2024 01/01/2024
2 02/01/2024 04/01/2024
3 05/01/2024 15/01/2024
4 05/01/2024 05/01/2024
5 05/01/2024 07/01/2024
6 10/01/2024 15/01/2024
7 12/01/2024 31/01/2024
8 16/01/2024 16/01/2024
9 18/01/2024 31/01/2024
10 18/01/2024 24/01/2024
11 22/01/2024 24/01/2024
12 24/01/2024 24/01/2024
13 29/01/2024 04/02/2024
14 30/01/2024 04/02/2024
15 31/01/2024 05/02/2024
I would like to know how many people are absent on a specific day.
On 23/01 for example, this should show me 4 as ID's 7, 9, 10 and 11 are absent on 23/01/2024.
Any advise?
Hi, @JVL
Create a new Table
Date = CALENDAR(MIN('Table'[Begindate]),MAX('Table'[Enddate]))
Create two measures, the following is DAX
Measure = IF(MAX([Begindate])<= MAX('Date'[Date]) && MAX([Enddate]) >= MAX('Date'[Date]),1)
Sum Example = COUNTROWS(FILTER(ALLSELECTED('Table'),[Begindate]<= MAX('Date'[Date]) && [Enddate] >= MAX('Date'[Date])))
Here is my preview
How to Get Your Question Answered Quickly
Best Regards
Yongkang Hua
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
105 | |
79 | |
69 | |
62 |
User | Count |
---|---|
142 | |
105 | |
103 | |
85 | |
70 |