Forum Discussion
Calculating the Average Days Difference
- Anonymous9 years ago
The easiest way to do this is to create a calculated column in combination with a date table. You will need a date table because you will need all dates in the specific period. You can either create a date table in your database, import it (and create it in Excel) or dynamically using DAX. For the last one, see for example: https://www.agilebi.com.au/blog/power-bi-date-dimension.
You will need to create a column (For exampe 'Workingday' in this date table where you specify for each day if it's a weekday. The column should contain a 0 or a 1 (1 for weekdays and 0 for weekenddays).
Then, your create a calculated column with the following formula:
Workingdays = calculate( sum(DimDate[Workingday]); DATESBETWEEN( DimDate[Date]; PutAwayHeaders[AssignmentDate]; PutAwayHeaders[CompleteDate])))As you can see I named my date table DimDate.
The last step is to create a measure with an average over this calculated column:
Average Workingdays = AVERAGE(Workingdays)
The easiest way to do this is to create a calculated column in combination with a date table. You will need a date table because you will need all dates in the specific period. You can either create a date table in your database, import it (and create it in Excel) or dynamically using DAX. For the last one, see for example: https://www.agilebi.com.au/blog/power-bi-date-dimension.
You will need to create a column (For exampe 'Workingday' in this date table where you specify for each day if it's a weekday. The column should contain a 0 or a 1 (1 for weekdays and 0 for weekenddays).
Then, your create a calculated column with the following formula:
Workingdays = calculate( sum(DimDate[Workingday]); DATESBETWEEN( DimDate[Date]; PutAwayHeaders[AssignmentDate]; PutAwayHeaders[CompleteDate])))As you can see I named my date table DimDate.
The last step is to create a measure with an average over this calculated column:
Average Workingdays = AVERAGE(Workingdays)
So I have the table which specifies day of the week, how would i write a new column to do the 1 for weekday and 0 for weekend