Forum Discussion

KG1's avatar
KG1
Resolver I
4 years ago
Solved

Working day calculation

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))
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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