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.
I think this is a similar problem, have a read and if you need help adjusting let me know
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.
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.
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.