Count of Tickets in X amount of hours

[v_mark]

I was trying to create a Card Visual each that has the following condition
Less than 24hrs, More than 72hrs, and more than 120hrs Total tickets created 

Eventually, I will be needing to put it on a matrix

I was wondering how can I write a DAX measure that I can use for each card? 

I have two tables 

Fact table that has CreatedDateTime and Ticket IDs
with *:1 relationship to

Dim_Date table 

[v_mark]

@VahidDM - I appreciate the response. So far there is no requirement about business hours and weekends.

I might look for a way to use DATEDIFF in a measure instead of a calculated column.  OR 

perhaps do the calculation back in PQ

[v-rzhou-msft · Accepted solution]

Hi @v_mark 

I create a sample to have a test.

Firstly, create a calculated table for slicer.

Slicer = {"Less than 24hrs", "More than 72hrs", "More than 120hrs"}

Measure:

Measure = 
VAR _SelectValue = SELECTEDVALUE(Slicer[Value])
VAR _LESS24hrs = CALCULATE(COUNT('Table'[Ticket IDs]),FILTER(ALL('Table'),DATEDIFF('Table'[CreatedDateTime],NOW(),HOUR)<24))
VAR _More72hrs = CALCULATE(COUNT('Table'[Ticket IDs]),FILTER(ALL('Table'),DATEDIFF('Table'[CreatedDateTime],NOW(),HOUR)>72))
VAR _More120hrs = CALCULATE(COUNT('Table'[Ticket IDs]),FILTER(ALL('Table'),DATEDIFF('Table'[CreatedDateTime],NOW(),HOUR)>120))
RETURN
SWITCH(_SelectValue,"Less than 24hrs",_LESS24hrs,"More than 72hrs",_More72hrs, "More than 120hrs",_More120hrs)

Result is as below.

Select More than 120hrs:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[VahidDM]

Hi @v_mark 

It seems you need to calculate the Business Hours between CreatedDateTime and Now.

You can use the business hours calculation dax formula:

https://www.vahiddm.com/post/calculating-business-hours-in-power-bi-using-dax

or if the business hour and weekend are not important, you can use DATEDIFF dax code and set that to calculate Hour.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!