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

Help with measurement



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);

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





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

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.



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'[Date] >= INT ( 'Table'[date_started] )
            && 'Calendar'[Date] < INT ( ComparisonDate )
            && 'Calendar'[WorkingDays] = 1
    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.




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.












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.