Select Specific Values from a row in a table based on a Date Condition

Hello,

I need to be able to identify which KPI Name has a value of M or H in the last consecutive 3 months within the available date values for that KPI. I have highlighted in red the KPI names that should be returned in this example. Notice that it dosent have to be the last 3 month from the current date but just where a date value is available as long as it includes an M or an H in 3 consecutives months. However, if a value ohter than M or H or a blank exists after any 3 consecutives month with values of M or H, then it shouldnt be counted. See KRI Name "Average resolution Time".
The solution could be just a simple Yes or No value that I could then use to filter the table.

I tried to include a sample Power BI file but I din't see that option available. Apologies

Here is the raw data.

KPI ID

KPI Name

Jan-24

Feb-24

Mar-24

Apr-24

May-24

Jun-24

Jul-24

Aug-24

Sep-24

Oct-24

Nov-24

Average Resolution Time

Customer Retention Rate

Customer Satisfaction Score (CSAT

Employee Turnover Rate

First Call Resolution (FCR)

Inventory Turnover

Net Promoter Score (NPS)

On-Time Delivery Rate

Operational Efficiency Ratio

Revenue Growth Rate

Thank you!

Need Help

Anonymous

1 year ago

Hi mmunozjr5 , hello Bibiano_Geraldo and danextian , thank you for your prompt reply!

Based on Bibiano Geraldo's solution, please create the calculated column as shown below:

ConsecutiveMH = 
VAR CurrentKPI = 'Table'[KPI Name]
VAR CurrentDate = 'Table'[Date]
VAR PreviousMonth1 = EDATE(CurrentDate, -1)
VAR PreviousMonth2 = EDATE(CurrentDate, -2)
VAR NextMonth1 = EDATE(CurrentDate, 1)
VAR ValueCurrent = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], CurrentDate)
VAR ValuePrevious1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth1)
VAR ValuePrevious2 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth2)
VAR ValueNext1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], NextMonth1)

VAR IsConsecutiveMH = 
    ValueCurrent IN {"M", "H"} &&
    ValuePrevious1 IN {"M", "H"} &&
    ValuePrevious2 IN {"M", "H"}


VAR OnlyMOrBlank = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "M" || 'Table'[Value]=BLANK()))
    ) = CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI)
    )

VAR OnlyHOrBlank = 
    CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "H" || 'Table'[Value]=BLANK()))
    ) = CALCULATE(
        COUNTROWS('Table'), 
        FILTER('Table', 'Table'[KPI Name] = CurrentKPI)
    )

VAR IsValidAfterSequence = 
    IF(
        OnlyMOrBlank || OnlyHOrBlank, 
        TRUE, 
        NOT(ValueNext1=BLANK() || ValueNext1 = "L")  
    )


RETURN


IF(
    IsConsecutiveMH && IsValidAfterSequence,
    "Yes",
    "No"
)

Result for your reference:

Best regards,

Joyce

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

MHHFinal.pbix45 KB

5 Replies

danextian
Super User
1 year ago
Hi mmunozjr5

First, your data needs to be in proper format. What you currenly have is easy for humans to read but not very usable for reporting. Data needs to be tabular. In the query editor, select the first two columns then right click and select unpivot other columns. Then Parse the date from the generate attribute/month column. You wil use this to eventually sort the periods (if not they will be sorted alphabetically wherein april will come first followed by august).

Then create a period table either in DAX or M (attached pbix uses DAX) and relate it to the fact table. Make sure to use the columns from the period table. Then create these measues

M count = CALCULATE ( COUNTROWS ( 'Table' ), KEEPFILTERS ( 'Table'[Value] = "M" ) ) M Count L3M = CALCULATE ( [M count], DATESINPERIOD ( Period[Start of Month], MAX ( Period[Start of Month] ), -3, MONTH ), ALL ( Period ) )

Please see attached pbix for the details.
unpivot and last three months.pbix26 KB
- mmunozjr5
  Frequent Visitor
  1 year ago
  Hello Danextian, thank you so much for taking the time to provide me with this solution. Is it possible to return just the 4 KPI names that I highlited in red in my original example? I am providing additional clarification. Here is the additional information. Thank you!
  
  Remember, if a value other than M or H or a blank exists after any 3 consecutives month with values of M or H, then it shouldn't be counted. See KRI Name "Average resolution Time".
  
  Business Rules
  A blank value or values after is ok as long as there is no other nonconsecutive values, so in this case Revenue Growth Rate counts
  
  We need exactly 3 consecutive 'M' or 'H' values.
  
  If there are non-'M'/'H' values (like 'L') or blanks immediately after this sequence, the KPI should be disqualified.
  
  Blanks after a valid 3-month sequence of 'M'/'H' are allowed as long as they don’t interrupt or add to the consecutive values.
  
  Here are the name of the tables and fields I am using
  
  Column Names
  'Monthly KPI Log'
  'Monthly KPI Log'[KPI Name]
  'Monthly KPI Log'[Value]
  'Monthly KPI Log'[Metric Collection Date]
  - Anonymous
    Not applicable
    1 year ago
    Hi mmunozjr5 , hello Bibiano_Geraldo and danextian , thank you for your prompt reply!
    
    Based on Bibiano Geraldo's solution, please create the calculated column as shown below:
    
    ConsecutiveMH = VAR CurrentKPI = 'Table'[KPI Name] VAR CurrentDate = 'Table'[Date] VAR PreviousMonth1 = EDATE(CurrentDate, -1) VAR PreviousMonth2 = EDATE(CurrentDate, -2) VAR NextMonth1 = EDATE(CurrentDate, 1) VAR ValueCurrent = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], CurrentDate) VAR ValuePrevious1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth1) VAR ValuePrevious2 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth2) VAR ValueNext1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], NextMonth1) VAR IsConsecutiveMH = ValueCurrent IN {"M", "H"} && ValuePrevious1 IN {"M", "H"} && ValuePrevious2 IN {"M", "H"} VAR OnlyMOrBlank = CALCULATE( COUNTROWS('Table'), FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "M" || 'Table'[Value]=BLANK())) ) = CALCULATE( COUNTROWS('Table'), FILTER('Table', 'Table'[KPI Name] = CurrentKPI) ) VAR OnlyHOrBlank = CALCULATE( COUNTROWS('Table'), FILTER('Table', 'Table'[KPI Name] = CurrentKPI && ('Table'[Value] = "H" || 'Table'[Value]=BLANK())) ) = CALCULATE( COUNTROWS('Table'), FILTER('Table', 'Table'[KPI Name] = CurrentKPI) ) VAR IsValidAfterSequence = IF( OnlyMOrBlank || OnlyHOrBlank, TRUE, NOT(ValueNext1=BLANK() || ValueNext1 = "L") ) RETURN IF( IsConsecutiveMH && IsValidAfterSequence, "Yes", "No" )
    
    Result for your reference:
    
    Best regards,
    
    Joyce
    
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    
    MHHFinal.pbix45 KB
Bibiano_Geraldo
Super User
1 year ago
Hi, as danextian said, your need to Unpivot columns first off all:

1. In power query, select the columns [KPI ID] and [KPI NAME] and then with right click on top of one, go to unpivot other columns as shown bellow:
Your can lear more about Unpivot here: Unpivot columns

2. Now your data should look like this:

3. Change types and rename columns as you need, make sure that the date column is in date format:

4. Close and apply

5. In your table, create a calculated column by using the following DAX code:
ConsecutiveMH = VAR CurrentKPI = 'Table'[KPI Name] VAR CurrentDate = 'Table'[Date] VAR PreviousMonth1 = EDATE(CurrentDate, -1) VAR PreviousMonth2 = EDATE(CurrentDate, -2) VAR ValueCurrent = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], CurrentDate) VAR ValuePrevious1 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth1) VAR ValuePrevious2 = LOOKUPVALUE('Table'[Value], 'Table'[KPI Name], CurrentKPI, 'Table'[Date], PreviousMonth2) RETURN IF( ValueCurrent IN {"M", "H"} && ValuePrevious1 IN {"M", "H"} && ValuePrevious2 IN {"M", "H"}, "Yes", "No" )

now you can add A matrix with Kpi name column in rows field and add a slicer with created column, and this should look like this:

NOTE: Make sure to replace columns and tables names with your owns.

If this help you, please give a kudo and mark as solution.

Thank you
- mmunozjr5
  Frequent Visitor
  1 year ago
  Hello Geraldo, thank you so much for taking the time to provide me with this solution. I am providing additional clarification to see if you could correct the issue with Average Resolution Time, and Customer Retention as based on the following rules they should not be returned on the table. Here is the additional information. THank you!
  
  Remember, if a value other than M or H or a blank exists after any 3 consecutives month with values of M or H, then it shouldn't be counted. See KRI Name "Average resolution Time".
  
  Business Rules
  A blank value or values after is ok as long as there is no other nonconsecutive values, so in this case Revenue Growth Rate counts
  
  We need exactly 3 consecutive 'M' or 'H' values.
  
  If there are non-'M'/'H' values (like 'L') or blanks immediately after this sequence, the KPI should be disqualified.
  
  Blanks after a valid 3-month sequence of 'M'/'H' are allowed as long as they don’t interrupt or add to the consecutive values.
  
  Here are the name of the tables and fields I am using
  
  Column Names
  'Monthly KPI Log'
  'Monthly KPI Log'[KPI Name]
  'Monthly KPI Log'[Value]
  'Monthly KPI Log'[Metric Collection Date]