Conditional Formating Matrix Comparing Previous Column

[robertnoble]

Hi,

I have the following matrix, the top row represents story points (as a bucket) and AVG time in days (measure). (The data is off due to missing data, but working to get that fixed).

I want to add icon conditional formating in such a way that if the previous bucket is larger, to put a warning icon and if it is larger than the previous bucket, put a green icon.

e.g. since 13's is only 3 days and 10 is 22 days, 13 value would show a warning sign. Whereas 20 would show green since 22 is larger than 3.

The final logic of the conditional formating seems straight forward, if() argument comparing values and returns 1 or 0 and then assign those to an icon.

My issue is in creating the measure, I cannot get it to calculate the value of the previous bucket to compare.

Conditional formatting = 
VAR storypoint =
    SELECTEDVALUE ( 'Issue Data'[Story Points] )
VAR currentstory =
    CALCULATE([Pt AVG Final], FILTER('Issue Data','Issue Data'[Story Points] = storypoint)
    )

VAR PTSum =
    addcolumns(
        SUMMARIZE('Issue Data', 'Issue Data'[Story Points]),
        "By Point",
        [Pt AVG Final]
    )
var prevpt =
    CALCULATE(MINx(topn(2,PTSum,'Issue Data'[Story Points]),'Issue Data'[Story Points]), ALL('Issue Data'), 'Issue Data'[Story Points])

var previousstory = CALCULATE([Pt AVG Final], Filter('Issue Data', 'Issue Data'[Story Points] = prevpt)

return
if(currentstory > previousstory, 1, 0)

My issue is that when trying to derive prevpt, it will return the same story point value instead of the the one below it (i.e. For 20, it should return 13, not 20 which it is doing now). Not sure if there is an issue with PTSum due to the slicers, although when I check DAX Studio, it seems to work correctly.

Also, if you know a better way to do this please do share. I was looking for various other ways to do this, but most other examples involve dates so it is a lot easier pulling previous month data but the reference here are numeric buckets (Story Points).

[parry2k]

@robertnoble I would recommend sharing pbix file using one drive/google drive and removing any sensitive information before sharing. Also, please explain the expected output.

[parry2k]

@robertnoble try this:

Conditional formatting = 
VAR currentstory = [Pt AVG Final]
VAR PTSum =
    addcolumns(
        SUMMARIZE(ALLSELECTED('Issue Data'[Story Points] ), 'Issue Data'[Story Points]),
        "By Point",
        [Pt AVG Final]
    )
var prevpt =
    MINx(topn(2,PTSum,'Issue Data'[Story Points]),'Issue Data'[Story Points])

var previousstory = CALCULATE([Pt AVG Final], 'Issue Data'[Story Points] = prevpt)

return
if(currentstory > previousstory, 1, 0)

[robertnoble]

Did not work. Looks like it is ignoring my filters and also prevpt is returning 73 for each story point bucket and causing (blank) for the previoustory. Applying it to the visual gets this: