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.
I am trying to create a card visual to show the total working days lost in the period
This period is 01/11/21 - 30/11/21
For those people off sick before the 01/11/21 I have created a new start date column to state the start date as 01/11/21 or the actual start date if started during the month of Nov.
If the sickness started on 01/11/21 and ended on 30/11/21 total working days should be 22.
When created a table to check the data and measure was working correctly I have a problem where the duration is showing 88 instead of 22
There is only 1 row in the data table which is related to that employee number showing 88
When I select don't summarize in the duration for in period reporting it changes to 22
The sum should be 22?
Here is the measure used to calculate working days
Solved! Go to Solution.
Hi @KG1 ,
Since you are creating a calculated column and its data type is numeric, it will be aggregated... You can change its aggreation function to MAX or MIN, or you can create a measeure as follows to get the unique value just as below screenshot. You can find the attachment for all details.
Duration =
MAXX (
ALLEXCEPT (
'Absence',
'Absence'[Person Number],
'Absence'[Start Date for in Period Count],
'Absence'[End Date for Report]
),
[Duration for in period reporting]
)
Best Regards
Hi @KG1 ,
Since you are creating a calculated column and its data type is numeric, it will be aggregated... You can change its aggreation function to MAX or MIN, or you can create a measeure as follows to get the unique value just as below screenshot. You can find the attachment for all details.
Duration =
MAXX (
ALLEXCEPT (
'Absence',
'Absence'[Person Number],
'Absence'[Start Date for in Period Count],
'Absence'[End Date for Report]
),
[Duration for in period reporting]
)
Best Regards
@KG1 , if these are selected dates from a day table, then you can have a static value
Selected working days =
var _max =maxx(allselected(date),date[date])
var _min =maxx(allselected(date),date[date])
return
Duration for in period reporting = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(_min,_max),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
Or Try like
Duration for in period reporting = sumx(values('Absence'[Person Number]) ,
COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR(Min('Absence'[Start Date for in Period Count]),Max('Absence'[End Date for Report])),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |