Forum Discussion
Select Specific Values from a row in a table based on a Date Condition
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.
Super UserHi 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.
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]
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.