Pricing analysis over time - need a change flag/indicator

[kahnailee]

I'm trying to track any/all price changes over a period of time across multiple suppliers. In doing so, I pivoted the data with the Date in the columns, SKU in the rows, and Cost within the values. That works perfectly for my needs, but I also need a flag that indicates if there's a change in price per SKU/row. I tried doing a MAX minus MIN for each line, which worked, but that wouldn't factor Supplier so it would compare all.

Here's a sample of the unpivoted data, with the highlighted portion being what I need to trigger a flag comparing day-over-day changes at both the SKU and Supplier level:

What's the best way of determining a "PriceChangeFlag" by each supplier and sku?

[Greg_Deckler]

I'm thinking something like:

Measure Flag = 
VAR __date = MAX([Data_Date]) //current date
VAR __sku = MAX([SKU]) //current sku
VAR __supplier = MAX([Supplier]) //current supplier
VAR __prevCostDate = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]<__date),[Data_Date])
VAR __prevCost = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]=__prevCostDate),[Cost])
RETURN
IF([Cost]<>__prevCost,1,0)

[kahnailee]

That would work if there was one sku, I believe, but I have thousands. Therefore, I get an error of "a single value for column 'Cost' cannot be determined". Any ideas?

[Greg_Deckler]

Sorry, missed an aggregation.

Measure Flag = 
VAR __date = MAX([Data_Date]) //current date
VAR __sku = MAX([SKU]) //current sku
VAR __supplier = MAX([Supplier]) //current supplier
VAR __prevCostDate = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]<__date),[Data_Date])
VAR __prevCost = MAXX(FILTER(ALL('Table'),[SKU]=__sku && [Supplier]=__supplier && [Data_Date]=__prevCostDate),[Cost])
RETURN
IF(MAX([Cost])<>__prevCost,1,0)

[kahnailee]

That works, but it doesn't factor each supplier. Meaning, it will look across all suppliers to see if there's a change overall, not looking at one supplier for the available dates. We're getting close though.

[Greg_Deckler]

That depends on the context of your visual. If you have a visual setup the way you do in your picture, then it should work. That is why I do a MAX([Supplier]) and then use that in the filters do make sure I am dealing with the correct supplier.

[kahnailee]

This is ideally how I'd like to see the data day over day. I'd also like to have the ability to filter out only those in which have changes (outside of those with zero's or blanks).

I've also put in a filter for Supplier so that should help reduce the anomalies, but I still need the filterable flag.

[v-shex-msft · Accepted solution]

HI @kahnailee,

You can try to use following measure to return tag based on diff between current and previous cost:

Diff Tag=
VAR currDate =
    MAX ( Table3[Date] )
VAR currSKU =
    SELECTEDVALUE ( Table3[Sku] )
VAR currSupplier =
    SELECTEDVALUE ( Table3[Supplier] )
VAR prevDate =
    CALCULATE (
        MAX ( Table3[Date] ),
        FILTER (
            ALLSELECTED ( Table3 ),
            [Date] < currDate
                && [Supplier] = currSupplier
                && [Sku] = currSKU
        )
    )
VAR prevCost =
    CALCULATE (
        MIN ( Table3[Cost] ),
        FILTER (
            ALLSELECTED ( Table3 ),
            [Date] = prevDate
                && [Supplier] = currSupplier
                && [Sku] = currSKU
        )
    )
VAR result =
    IF ( prevCost <> BLANK (), MAX ( Table3[Cost] ) - prevCost, 0 )
RETURN
    IF ( result > 0, "↑", IF ( result < 0, "↓", "-" ) )

In addition, you can also create a calculated column with dynamic hex color code based on diff, then use conditional formatting feature to add color to original field value.

Conditional formatting in tables

Regards,

Xiaoxin Sheng

[kahnailee]

@Xiaoxin Sheng

Wow, that's an interesting solution. Do you happen to have the file you put together from the screenshot? If so, do you mind sharing?

[v-shex-msft]

HI @kahnailee,

I Just add measure to value field and enable conditional formatting based on calculated column. I attached sample file below.

Regards,

Xiaoxin Sheng

[Ashish_Mathur]

Hi,

So will you apply a filter on any one supplier and then see the result?  If you have 500 suppliers, you will have to filter 500 times.  Am i missing something?