Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
acorr
Frequent Visitor

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. 

acorr_1-1701122878624.png

 

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. 

acorr_0-1701122850824.png

 

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

1 REPLY 1
amitchandak
Super User
Super User

@acorr , You can use nework Days and calculate at row level like

AVERAGEX(Table, NETWORKDAYS('Export'[CreatedTime] , 'Export'[CompletionTime],1))

 

Or you can refer this for business Hours

https://exceleratorbi.com.au/calculating-business-hours-using-dax/

 

 

for networkdays

Calculating Business Days with and without NETWORKDAYS DAX Function | 2023 Guide: https://www.youtube.com/watch?v=Qs03ZZXXE_c

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors