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 sta...
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

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