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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
acorr
Regular 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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.