cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Aekstro2 Frequent Visitor
Frequent Visitor

Help with measurement

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 isSmiley Happy

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

 

4 REPLIES 4
Super User
Super User

Re: Help with measurement

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/...

Community Support Team
Community Support Team

Re: Help with measurement

Hi @Aekstro2,

 

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Aekstro2 Frequent Visitor
Frequent Visitor

Re: Help with measurement

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

Aekstro2 Frequent Visitor
Frequent Visitor

Re: Help with measurement

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.

 

number.PNGsys_created_on.PNGu_resolved_on.PNG 

 

 

 

 

 

 

 

 

 

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