Boolean Column: SLA's Missed within the Current Year

[TaylorClose]

Hi,

I am trying to create a column for a customer account table that calculates how many tickets in a separate table have the status "SLA Missed" and were submitted within the current year, and evaluates to "True" if the number is greater than 0.

Tables:
'Excel - Ticket Data' - Contains a line per ticket
'Calendar' - Normal Calendar Table

Columns:
'Calendar'[Date] - Has normal dates within it.
'Excel - Ticket Data'[Was SLA ever missed? - Has the values "Yes" and "No" within it
'Excel - Ticket Data'[Date Submitted] - Has normal dates within it.

My first attempt at the calculation for the new column is as such:

HAS MISSED SLAS = CALCULATE(COUNTROWS('Excel - Ticket Data'), AND(FILTER('Excel - Ticket Data','Excel - Ticket Data'[Was SLA ever missed?] = "Yes"), FILTER('Excel - Ticket Data', 'Excel - Ticket Data'[Date Submitted] = DATESINPERIOD('Calendar'[Date],1/1/2020, 12, MONTH)))) > 0

The desired result is a boolean column at the account level that returns a "True" if there are tickets past SLA within the year, and "False" if not.

Any help would be appreciated!

[Fowmy · Accepted solution]

@TaylorClose 

I modified your formula as: You can change the date "1/1/2020" to today to get the past 12 months if you need it.

HAS MISSED SLAS = 

VAR _COUNT = 
    CALCULATE(
        COUNTROWS('Excel - Ticket Data'), 
        'Excel - Ticket Data'[Was SLA ever missed?] = "Yes", 
        DATESINPERIOD('Calendar'[Date],"1/1/2020", -12, MONTH)
    )
RETURN

  IF(
      _COUNT > 0,
      TRUE(),
      FALSE()
  )

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[TaylorClose]

Had to tweak this a bit to make Date submitted the active relationship, but this now works.  Thank you!

Taylor