Help with measurement

[Anonymous]

Hi, 

I want to calculate the amount of closing hours for a ticket but is filtered with "is a workday". 

The current measurement we use is this one, which calculates the amount of closing days, but I want closing hours.

Closing hours exclude weekends = CALCULATE(sum('Snow Calendar'[Is a workday]);
FILTER('Snow Calendar';
'Snow Calendar'[Date] >= FIRSTDATE('SNOW(sn_sm_finance_request)'[sys_created_on].[Date]) &&
'Snow Calendar'[Date] < LASTDATE('SNOW(sn_sm_finance_request)'[u_resolved_on].[Date]))) 

We also have this measurement, which calculates the closing hours, but is including weekends and is not calculating the working hours of a workday.

Closing hours = DATEDIFF([sys_created_on]; 'SNOW(sn_sm_finance_request)'[u_resolved_on]; HOUR)

The the columns we use is:

- 'SNOW(sn_sm_finance_request)'[sys_created_on] (returns the value when the ticket was opened with date and time)

- 'SNOW(sn_sm_finance_request)'[u_resolved_on] (returns the value when the ticket was closed with date and time)

- 'SNOW Calendar'[Is a workday] (return what a workday is. The function we use for this is:)

Is a workday = SWITCH(
WEEKDAY('Snow Calendar'[Date];2);
6;0;
7;0;
1)

- 'SNOW Calendar'[Date] (returns all dates from 2015 until now)

Snow Calendar = CALENDAR(DATE(2015;1;1); NOW()) 

I really hope you can help me with this. If you need some more information or want me to change any existing measurement like the "Is a workday" please let me know. 

Regards

Albin

[v-yulgu-msft]

Hi @Anonymous,

Please provide sample data and expected result as suggested in this blog so that we can try to reproduce a similar scenario and test for you.

Regards,

Yuliana Gu

[Anonymous]

Hi  @v-yulgu-msft,

Of course, I will provide better information.

Just to clarify, a workday is from 9-17 every day except weekends. 

Here are the three most important columns, which I think.

 

Every number here was created (sys_created_on) and resolved at some point (u_resolved_on).

I want two different calculations:

- One calculation for the open hours for a ticket from it has been created until now and includes the workday. Therefore, it only calculates the time between 9-17 and ignores weekends (notice that I did not write this in the original post. This is something I needed to create today due to a request).

- Another calculation for the closing hours from when it was created until when it was resolved. Which includes a workday. Same as above from 9-17 and ignores weekends.

Hope this clarifies something. 

Regards 

Albin

[Stachu]

I think this is a similar problem, have a read and if you need help adjusting let me know
https://community.powerbi.com/t5/Desktop/Working-hours-without-non-working-hours-no-weekends-and-no/...

[Anonymous]

Hi @Stachu,

I'm sorry for the late replay, but I've been out of office and couldn't replay.

I tried this and took a look, but there where some things I didin't really get from this post. 

I copied it into my Power BI and changed the columns to mine. 

But there appiered some errors. Non of the VAR function didn't work (it showed a red error text under the VAR) , as well as when a VAR calculation was placed in another VAR function. For exmple this (the red text shows where it errored): 

VAR DaysInScope =
    FILTER (
        'Calendar',
        'Calendar'[Date] >= INT ( 'Table'[date_started] )
            && 'Calendar'[Date] < INT ( ComparisonDate )
            && 'Calendar'[WorkingDays] = 1

RETURN
    COUNTROWS ( DaysInScope ) * 8
        + HourDifference

I didn't either find any information about the WorkingDays column (blue text). Therefore, I didn't understand what to replace it with.

If you could clarify this for me it would be great and I can test the function again.

Regards 

Albin