Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
PBI_V2
Helper IV
Helper IV

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

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.

 

PBI_V2_0-1714059747584.png

 

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

1 ACCEPTED SOLUTION
v-kaiyue-msft
Community Support
Community Support

Hi @PBI_V2 ,

Based on your description, I created this data.

vkaiyuemsft_0-1714101338011.png


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)

vkaiyuemsft_1-1714101378271.png

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.

View solution in original post

1 REPLY 1
v-kaiyue-msft
Community Support
Community Support

Hi @PBI_V2 ,

Based on your description, I created this data.

vkaiyuemsft_0-1714101338011.png


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)

vkaiyuemsft_1-1714101378271.png

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.

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.