cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kahnailee Frequent Visitor
Frequent Visitor

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:

 

2018-11-23_9-36-24.jpg

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Pricing analysis over time - need a change flag/indicator

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.

2.PNG

Conditional formatting in tables


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

10 REPLIES 10
Super User
Super User

Re: Pricing analysis over time - need a change flag/indicator

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)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kahnailee Frequent Visitor
Frequent Visitor

Re: Pricing analysis over time - need a change flag/indicator

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?

Super User
Super User

Re: Pricing analysis over time - need a change flag/indicator

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)

I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kahnailee Frequent Visitor
Frequent Visitor

Re: Pricing analysis over time - need a change flag/indicator

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.

Super User
Super User

Re: Pricing analysis over time - need a change flag/indicator

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.


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

kahnailee Frequent Visitor
Frequent Visitor

Re: Pricing analysis over time - need a change flag/indicator

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).

 

2018-11-23_11-28-13.jpg

 

 

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

Super User
Super User

Re: Pricing analysis over time - need a change flag/indicator

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?


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Community Support Team
Community Support Team

Re: Pricing analysis over time - need a change flag/indicator

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.

2.PNG

Conditional formatting in tables


Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |

View solution in original post

kahnailee Frequent Visitor
Frequent Visitor

Re: Pricing analysis over time - need a change flag/indicator

@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?

Helpful resources

Announcements
Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 305 members 2,783 guests
Please welcome our newest community members: