show icons based on previous value of measure

[rautaniket0077]

Hi Team,

As you can see in the attached image my data is having rules, monday date representing the week ( for eg:- Week of 14-Oct) and score, i need to show the icons comparing current score with previous score but there are some weeks when rules are not executed for those weeks i need to show "-", now the case when previous value is "-" then compare current value with previous non hyphen "-" value.
Note -

1) score is the measure and it is calculated as (total records - bad records) *100/ total records 

2) you can use the sort column to sort the Week Monday

data-

Rule	Week Monday	Total Records	Bad Records	Sort
A	Week of 30-Sep	100	10	1
B	Week of 30-Sep	90	8	1
C	Week of 30-Sep	80	20	1
A	Week of 07-Oct			2
B	Week of 07-Oct	50	2	2
C	Week of 07-Oct			2
A	Week of 14-Oct	90	5	3
B	Week of 14-Oct	70	25	3
C	Week of 14-Oct	200	6	3
A	Week of 23-Sep	300	5	0
B	Week of 23-Sep	30	10	0
C	Week of 23-Sep	400	8	0

[rautaniket0077]

Hi @lbendlin , @Greg_Deckler , @bhanu_gautam please help me here. 

[rautaniket0077]

Hi @bhanu_gautam ,

Thank you for the reply, here the score is not column its a measure and when i am trying to calculate the previous score its giving me the blank and one more thing i need to show the data for the last 7 weeks only so one date filter is applied on this matrix vasual.  

[bhanu_gautam]

@rautaniket0077 , Try updated one to handle blanks

Create a measure to compute the score:

Score =
IF(
ISBLANK(SUM('Table'[Total Records])) || ISBLANK(SUM('Table'[Bad Records])),
BLANK(),
(SUM('Table'[Total Records]) - SUM('Table'[Bad Records])) * 100 / SUM('Table'[Total Records])
)

Create a measure to get the previous non-hyphen score:

Previous Score =
VAR CurrentSort = MAX('Table'[Sort])
VAR CurrentRule = MAX('Table'[Rule])
RETURN
CALCULATE(
[Score],
FILTER(
'Table',
'Table'[Sort] < CurrentSort &&
'Table'[Rule] = CurrentRule &&
NOT(ISBLANK([Score]))
),
LASTNONBLANK('Table'[Sort], [Score])
)

Create a measure to compare the current score with the previous score and display the appropriate icon:

Score Comparison Icon =
VAR CurrentScore = [Score]
VAR PrevScore = [Previous Score]
RETURN
IF(
ISBLANK(CurrentScore),
"-",
SWITCH(
TRUE(),
ISBLANK(PrevScore), "-",
CurrentScore > PrevScore, "↑",
CurrentScore < PrevScore, "↓",
"→"
)
)

[rautaniket0077]

Hi @bhanu_gautam ,

so sort (and Week of 14-Oct) is coming from date table and rules are from separate table. Apart from this there is no blanks in data but since i need to show  last 7 weeks if particular rule is not executed in that week then i need to show that week as well with "-".

[bhanu_gautam]

@rautaniket0077 , Try using below measures 

Create a measure to get the previous score, skipping any weeks where the score is not available:

Previous Score =
VAR CurrentWeek = MAX('Table'[Sort])
VAR PreviousWeek =
CALCULATE(
MAX('Table'[Sort]),
FILTER(
'Table',
'Table'[Sort] < CurrentWeek && NOT(ISBLANK('Table'[Score]))
)
)
RETURN
CALCULATE(
MAX('Table'[Score]),
FILTER(
'Table',
'Table'[Sort] = PreviousWeek
)
)

 

 

Create a measure to compare the current score with the previous score and display the appropriate icon:

Score Icon =
VAR CurrentScore = MAX('Table'[Score])
VAR PrevScore = [Previous Score]
RETURN
IF(
ISBLANK(CurrentScore),
"-",
SWITCH(
TRUE(),
ISBLANK(PrevScore), "-",
CurrentScore > PrevScore, "🔼", // Up arrow icon
CurrentScore < PrevScore, "🔽", // Down arrow icon
"➖" // Dash icon for no change
)
)

Add a table or matrix visual to your report and include the Week Monday, Rule, Score, and Score Icon columns/measures.