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

Support Hours by month, ticket and customer

Good Morning,

I need to create a table visulation that displays the customer, support hours used by month (within the current calendar year), total support hours used (within the current calendar year), and the remaining support hours available.

Each customer is allowed 50 support hours during a calendar year. The support hours available is 50 - total support hours used (within the current calendar year).  Each customer may have multiple tickets and the support hours used within each ticket can increase or decrease throughout the month, therefore I need the most recent values each month. 

If the customer doesn't have any hours used, I'd rather them not be listed in the table.

I've tried using several measures, but I just can't seem to get it worked out.

Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Support Hours by month, ticket and customer

Ahh, I see now. Ok, I think I've got it. It's more complicated than I thought. I might have over-complicated as well; there might be potential to simply the solution and combine some of these fields together. In addition to the "Hours Used Flag" I created earlier, I created four more columns in the data set:

 

Month = MONTH('Support Hours 2'[Date])
 
YEAR = YEAR('Support Hours 2'[Date])
 
Hours Used Change =
LOOKUPVALUE(
'Support Hours 2'[Hours Used],
'Support Hours 2'[Ticket #],
'Support Hours 2'[Ticket #],
'Support Hours 2'[Month],
'Support Hours 2'[Month] - 1,
'Support Hours 2'[Hours Used Flag],
1
)
 
Hours =
IF (
'Support Hours 2'[Hours Used Flag] = 1,
IF ( ISBLANK('Support Hours 2'[Hours Used Change]),
'Support Hours 2'[Hours Used], 'Support Hours 2'[Hours Used] - 'Support Hours 2'[Hours Used Change])
)
 
"Hours Change" basically computes the hours for a given ticket for that month, rather than the cumulative like I had earlier. Then, the "Hours" takes the value it's supposed to from those two columns, depending on whether its the first month of hours for a ticket, or not.
 
Finally, you just need to update the references in the visuals to the new "Hours" column from the original field. Additionally, be sure to update the reference in the "Remaining Hours" column. Here's the output:
 
Support Hours 2.PNG
 
Let me know if this works for you.
 
Ben
7 REPLIES 7
bhpage Regular Visitor
Regular Visitor

Re: Support Hours by month, ticket and customer

Hi,

 

I set up a basic little dataset that looks like this:

Support Hours 1.PNG

 

I also created a calculated column for "Year" once I got to PowerBI:

Year = YEAR('Support Hours'[Date])

 

Basic aggregation will sum the "Hours Used" column into our "Hours Used per Month" column. I had to create the other two metrics we're looking for as calculated measures:

 

Hours Used Year = SUMX( FILTER(FILTER(ALL('Support Hours'), 'Support Hours'[Year] = SELECTEDVALUE('Support Hours'[Year])), SELECTEDVALUE('Support Hours'[Name]) = 'Support Hours'[Name]), 'Support Hours'[Hours Used Month] )
 
Remaining Support Hours = 50 - 'Support Hours'[Hours Used Year]
 
I hard-coded the value of 50 in that calculation, but you could handle this with a paramter or in the data source. Here's the output. The only trick I had to employ was to filter on "Hours Used Year" is not blank.
Support Hours 2.PNG
 
Let me know if this works for you.
 
Thanks,
Ben
 
LSUGirl Frequent Visitor
Frequent Visitor

Re: Support Hours by month, ticket and customer

@bhpage - Thank you for helping me!  The issue I'm having a hard time resolving is getting the latest value for each ticket within each month.  Referencing the example you provided, the last value entered for Joe is Jan. is 1 thus, the value for Jan. should be 1.  I apologize for not explaining this very well.

I also realize that I forgot to mention that each ticket's hours must be subtracted from the ticket's hours the previous month.  I included some data as an example...hopefully, it helps some.

 

example.png

 

Thank you!

bhpage Regular Visitor
Regular Visitor

Re: Support Hours by month, ticket and customer

In your example, shouldn't Joe's value for Feb be 2 (ticket A1 on 2/10) and Susan's be 5 (ticket A2 on 2/5)? You have them in your chart at the bottom as 1 and 3. Or am I missing something still?

bhpage Regular Visitor
Regular Visitor

Re: Support Hours by month, ticket and customer

If that's the case, I think I have a solution. I created a calculated column that flags the records to pull for each ticket/customer/month:

 

Hours Used Flag =
IF (
'Support Hours 2'[Date] =
MAXX (
FILTER (
FILTER (
ALL ( 'Support Hours 2' ),
'Support Hours 2'[Ticket #] = EARLIER ( 'Support Hours 2'[Ticket #] )
),
MONTH ('Support Hours 2'[Date]) = MONTH ( EARLIER ( 'Support Hours 2'[Date] ) )
) ,'Support Hours 2'[Date]
)
,1
,0
)
 
From there, you only need to set up your view how you'd like to see each, and then add this as a visual filter set to 1. Here's the output:
 
Support Hours.PNG
 
Let me know if this works or I'm still off.
 
Thanks,
Ben
LSUGirl Frequent Visitor
Frequent Visitor

Re: Support Hours by month, ticket and customer

@bhpage, Hi, the value for Feb needs to only show the hours used in Feb. - so I'm not double counting hours.  The overall total hours used will be the sum of hours used each month.  Hope that makes sensebut please let me know if I need to clarify more.

Thank you

Highlighted
bhpage Regular Visitor
Regular Visitor

Re: Support Hours by month, ticket and customer

Ahh, I see now. Ok, I think I've got it. It's more complicated than I thought. I might have over-complicated as well; there might be potential to simply the solution and combine some of these fields together. In addition to the "Hours Used Flag" I created earlier, I created four more columns in the data set:

 

Month = MONTH('Support Hours 2'[Date])
 
YEAR = YEAR('Support Hours 2'[Date])
 
Hours Used Change =
LOOKUPVALUE(
'Support Hours 2'[Hours Used],
'Support Hours 2'[Ticket #],
'Support Hours 2'[Ticket #],
'Support Hours 2'[Month],
'Support Hours 2'[Month] - 1,
'Support Hours 2'[Hours Used Flag],
1
)
 
Hours =
IF (
'Support Hours 2'[Hours Used Flag] = 1,
IF ( ISBLANK('Support Hours 2'[Hours Used Change]),
'Support Hours 2'[Hours Used], 'Support Hours 2'[Hours Used] - 'Support Hours 2'[Hours Used Change])
)
 
"Hours Change" basically computes the hours for a given ticket for that month, rather than the cumulative like I had earlier. Then, the "Hours" takes the value it's supposed to from those two columns, depending on whether its the first month of hours for a ticket, or not.
 
Finally, you just need to update the references in the visuals to the new "Hours" column from the original field. Additionally, be sure to update the reference in the "Remaining Hours" column. Here's the output:
 
Support Hours 2.PNG
 
Let me know if this works for you.
 
Ben
LSUGirl Frequent Visitor
Frequent Visitor

Re: Support Hours by month, ticket and customer

Wow - this is perfect.  Thank you so much!