Forum Discussion

LewisH's avatar
LewisH
Helper II
9 years ago
Solved

Calculating the Average Days Difference

I have a table called 'Put Away headers' and two fields Called 'Assignment Date' and 'Complete Date'. I would like to calculate an overall average of the days difference between these, excluding week...
Need Help
  • Anonymous's avatar
    Anonymous
    9 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) 

     

Anonymous's avatar
Anonymous
Not applicable
9 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) 

 

  • LewisH's avatar
    LewisH
    Helper II
    9 years ago

    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

     

  • LewisH's avatar
    LewisH
    Helper II
    9 years ago

    Workingdayss = CALCULATE(sum('Invoked Function'[WorkingDays]), DATESBETWEEN('Invoked Function'[Date], 'Put Away Headers'[Assignment_Date],'Put Away Headers'[Complete_Date]))

     

    I've tried this but i'm getting this error

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I think you accidentally created a measure instead of a column. Try again with a column using the same syntax (so right click on the table > new column). Then, create a measure with for Average Workingdays = AVERAGE(Workingdayss). 

      • LewisH's avatar
        LewisH
        Helper II
        9 years ago

        following your example I've created a table and populated workingday with either a 0 or 1. 

        Secondly I'm trying to create a new column in that table called 

        Workingdays = CALCULATE(sum('Invoked Function'[WorkingDay]), DATESBETWEEN('Invoked Function'[Date], 'Put Away Headers'[Assignment_Date],'Put Away Headers'[Complete_Date]))

         

        I havn't done the measure yet but the new column isn't working :)