Total Working Days Calculation in PowerBi

Question

Snapshot of Data

I have the that has some data related to employee start date and end date for specific task

How to calculate "Total Working days" using the above data in PowerBi

The expected ouput would be number of days this employee worked (according to date )

so for example 12/2/2018 12/3/2018 = 1 working Day

but if same record repeated this would remain one working day

Output from Formula

Screenshot of PBX File

TJ_ TJ_ · Accepted Answer · 2018-12-16T16:40:18

You can calculate the working day in various ways, I'll give you two options (both are calculated columns):

WorkingDays1 = Tasks[EndTime] - Tasks[StartTime]

(Assuming it's a date column type), you can add .DATE to be sure behind the column)

OR

WorkingDays2 = DATEDIFF(Tasks[StartTime];Tasks[EndTime];DAY)

If you want to correct the amount, for the same day, you can use an IF statement:

WorkingDays3 = 
  IF (
     Tasks[StartTime] = Tasks[EndTime];
     1;
     DATEDIFF ( Tasks[StartTime]; Tasks[EndTime]; DAY )
  )

If you wish to SUM the days, in a measure, you can SUM one of the generated columns above, OR you can create a measure which doesn't need such column: It iterates over the rows, and caculates the correct amount:

SUM WorkingDay =
     SUMX (
        Tasks;
        IF (
          Tasks[StartTime] = Tasks[EndTime];
          1;
          DATEDIFF ( Tasks[StartTime]; Tasks[EndTime]; DAY )
        )
     )

Result:

Note: These calculations do not take the weekends, or holidays into account.

Edit:

If you only want a unique list of dates, with the working days, you can use something like this. It itarates over the start dates:

SUM WorkingDay2 =
SUMX (
    VALUES ( Tasks[StartTime] );
    IF (
        DATEDIFF ( Tasks[StartTime]; CALCULATE ( MAX ( Tasks[EndTime] ) ); DAY ) = 0;
        1;
        DATEDIFF ( Tasks[StartTime]; CALCULATE ( MAX ( Tasks[EndTime] ) ); DAY )
    )
)

Edit 2:

If you want to create an unique list per day, you'll have to get rid of the time part in the datetime column. There are several ways to do this, but one option is to create a new column:

StartDate = Tasks[StartTime].[Date]

Then, you can adjust the measure by iteration over the dates, instead of the datetimes:

SUM WorkingDay3 = 
SUMX (
    VALUES ( Tasks[StartDate] );
    IF (
        DATEDIFF ( Tasks[StartDate]; CALCULATE ( MAX ( Tasks[EndTime] ) ); DAY ) = 0;
        1;
        DATEDIFF ( Tasks[StartDate]; CALCULATE ( MAX ( Tasks[EndTime] ) ); DAY )
    )
)

Total Working Days Calculation in PowerBi

1 Answers