Power BI DAX - Consecutive 3 months logic for calculation & Reset calculation every Year

[PBI_V2]

Hello All,

We are developing SLA Performance Dashboard in which we need to check whether a SLA is eligible for paying credit amount to customer or not. . Following are the conditions for credit eligibility:

1) There is a credit eligibility start month for this example - it is May-2023. so we will not consider any month before May 2023 for calculation.

2) If any SLA is not compliant for 3 consecutive months (from May 2023 & After ) then that month is eligible for credit. In this example,

July 2023  is eligible for credit because May 2023, Jun 2023, July 2023 are not compliant for continously 3 months.

3) Again Dec 2023 is eligible for Credit as Oct 2023, Nov 2023 & Dec 2023 are  not compliant for continously 3 months.

4)  Credit calculation need to be reset every year. In below example, we can see Nov 2023,Dec 2023 & Jan 2024 are continously non compliant but still Jan is not eligible for credit because every next year calculation need to start from January.

We need your help in creating a Calculated column or Measure which calculate whether a month is eligble for credit or not.

Thanks,

PBI V2

[v-kaiyue-msft · Accepted solution]

Hi @PBI_V2 ,

Based on your description, I created this data.

Create a calculated column to obtain results based on conditions.

Credit Eligibility =
VAR CurrentMonth = MONTH([date])
VAR Previous2MonthsCompliant = CALCULATE(COUNTROWS(FILTER('Table', [is compliant] = "no" && MONTH([date]) >= CurrentMonth - 2 && MONTH([date]) <= CurrentMonth)), FILTER('Table ', YEAR([date]) = YEAR(EARLIER([date]))))
RETURN
IF(Previous2MonthsCompliant = 3 && 'Table'[date] >= DATE(2023,5,1), 1, 0)

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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