Accumulated Monthly SLA Calculation Between 2 Years

[zahidah_mabd]

Hi, I'm new to power bi. I want to visualize the monthly SLA between 2021 and 2022 using a column chart.

I have calculated the monthly SLA which accumulates the ticket data from the previous months from starting from January. I have created a separate date table:

Calendar = CALENDARAUTO()

The SLA formula consists of 3 measures:

1. The number of non-violated incidents

non-violate 22 = 
CALCULATE (
    COUNT ( 'All Tickets 2022'[Incident Number]),

    'All Tickets 2022'[Violation] IN { "No" }, 
    FILTER (
        ALLSELECTED ( 'All Tickets 2022' ),
        'All Tickets 2022'[Open Date] <= MAX ( 'Calendar'[Date])
    )
)

2. Total number of incidents

incident num 22 = 
CALCULATE (
    COUNT ( 'All Tickets 2022'[Incident Number]), 
    FILTER (
        ALLSELECTED ( 'All Tickets 2022' ),
        'All Tickets 2022'[Open Date] <= MAX ( 'Calendar'[Date] )
    )
)

3. SLA 

SLA new 22 = 
DIVIDE(
[non-violate 22],[incident num 22]
)

Currently, I'm using separate source file for 'All Tickets 2021' and 'All Tickets 2022'. Both tables has the SLA calculation measures. So far the calculation works fine since each source table only has tickets dates from the specific year so the SLA will only be accumulated for that year. However, I want to combine the data from both years into one source file (1 excel sheet). 

How do I make sure that the accumulated values are based on the specific year whithout including the previous year? 

For example: SLA 2022 will only calculate and accumulate the values from January 2022 until the latest month of that year (March 2022). I dont want December 2021 to be included.

Meanwhile, SLA 2021 will accumulate from January 2021 until December 2021

How do I calculate the SLA for both 2021 and 2022? I think I might need to tweak the measures to select only dates for 2021 in SLA 2021 while 2022 dates for SLA 2022. I might need to do 2 SLA calculations for specific year in the same table but Im not sure how to filter or select the specific values in the [Open Date] column. Supposed the name of the combined years table is "All Tickets 21_22".

Can anyone help me? 

Thank you so much in advance 🙂

[v-yalanwu-msft]

Hi, @zahidah_mabd ;

Is your problem solved? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @zahidah_mabd ;

You could add a condition in your measure ,try it.

non-violate 22 = 
CALCULATE (
    COUNT ( 'All Tickets 2022'[Incident Number]),

    'All Tickets 2022'[Violation] IN { "No" }, 
    FILTER (
        ALLSELECTED ( 'All Tickets 2022' ),Year([Open Date])=Year(max([Open Date]))&&
        'All Tickets 2022'[Open Date] <= MAX ( 'Calendar'[Date])
    )
)

If not right , can you share me a simple file and the result what you want to outpout?

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@zahidah_mabd , along with common date , you might need common incident and status dimesion too , You can have measure like

SLA new =
DIVIDE(
[non-violate 22]+ [non-violate 21],[incident num 22]+ [incident num 21]
)

Another way is to append the tables in power query 

Append : https://radacad.com/append-vs-merge-in-power-bi-and-power-query