Forum Discussion

kahnailee's avatar
kahnailee
Icon for Advocate I rankAdvocate I
7 years ago
Solved

Pricing analysis over time - need a change flag/indicator

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?

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

10 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      kahnailee
      Icon for Advocate I rankAdvocate I
      7 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        7 years ago

        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)