Forum Discussion

acorr's avatar
acorr
Frequent Visitor
2 years ago

Average Measure/Column Troubleshooting

Hi all, 

 

Thanks so much for taking the time to read this. I am trying to 1) create measures or columns that show the business days and business hours between creation date/time and completion date/time (and replicate this for a few other time stamped fields and display the average time spent) and 2) I want to display the averages of these results that are filtered for different timeframes and groups (likely set up as card or KPI visuals.)

 

My issues - 

1) I appear to be able to calculate the business days )_BizDaysToResolve_), but I can't get these to correctly show an average in any visualization or correctly calculate business hours. I have a date table that the business hour measure references. 

 

2) On the flip side, I am able to show an average on a card for business hours (_AvgBizHoursToResolve), BUT the calculation for the measure/column in the above table doesn't look right. 

 

Calc for business days: 

_BizDaysToResolve_ = NETWORKDAYS (
SELECTEDVALUE (
    'Export'[CreatedTime] ),
    SELECTEDVALUE (
        'Export'[CompletionTime] ), 1, Holidays
) -1
 

Calc for business hours: (I don't think this is right):

_BizHours TTR =
VAR _Start = 'Export'[CreatedTime]
Var _End = 'Export'[CompletionTime]
Return SUMX(
            CALCULATETABLE(
                    'Date',
                    DATESBETWEEN('Date'[Date],_Start,_End),
                    'Date'[Workday] = 1
            ),
            MAX(MIN('Date'[End],_End) - MAX('Date'[Start],_Start),0) * 24
)
 
Also tried: 
_AvgBizHoursToResolve =
CALCULATE (
   AVERAGE ( 'Export'[_BizHours TTR] ),
   'Export'[_BizHours TTR] > 0
)
I get this error message for thi sone: Column '_BizDaysToResolve_' in table 'Export' cannot be found or may not be used in this expression.
 
I am new and I really appreciate your looking at this and your help! 🙂
 

-A

Need Help