Working day calculation

[KG1]

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

Duration for in period reporting = COUNTROWS(FILTER(ADDCOLUMNS(CALENDAR('Absence'[Start Date for in Period Count],'Absence'[End Date for Report]),"WorkDay", if(WEEKDAY([Date],2) <6,1,0)),[WorkDay] =1))

[Anonymous · Accepted 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

[amitchandak]

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