Identify date of first change in value of another column - Price changes

[shoaibalvi]

Hi all,

I have a small issue - I am terrible at DAX (just beginning) so no wonder I can't get it to work, but eager to learn!

I have a line chart with small multiples enabled. What this line chart does is, it tracks average of "Gross Price per Product" (y-axis) over time ("Billing Date" on x-axis) for each customer ("Customer Name" on small multiples). I want to be able to see that if there was any price change, did it occur consistently around the same time for all customers?

Though the line chart with its small multiple clearly shows the fluctuations in prices over time for each individual customer, its difficult to establish whether this occured on or around the same date unless I have a verticle line running across the visualisation that dynamically moves according to the earliest date on which a price change was detected. 

I have figured this line can be added using "Add further analysis" section, and then adding a constant line on X-axis. However, having a "constant" line defeats the purpose, and I am missing a Measure here which would tell power BI to drop the vertical line at the earliest point in the timeline when a price change was noticed. How do I go about achieving that? If i just pull in "Billing Date" and set it to "Earliest", it just drops a line at the starting point of the X-axis which is of course useless.

To simplify, a sample dashboard (Pbix) file, and an anonymised source table have been uploaded here.

In focus mode, the "change in gross price per customer over time" visualisation looks like this at the moment if you select the first bar in the top left visual titled "Number of distinct price points per product"

Now I need verticle lines running across the whole visualisaiton (not customer specific) that identify the point in time when the gross price showed the variation for the FIRST TIME only. Like this:

I have just drawn two lines in Teal and Purple to match the data line colors in the visualisation (apologies for the crookedness; did that using the snippet tool!). But the purpose is that PBI reads the data and identifies that under each distribution channel (HC or PS), when was the first time the price showed a change and then draws a verticle lines throughout for the same colour; this will help users have a reference point as they scroll down through the small multiples to view different customers. The purpose is that users should be able to readily identify if any particular customer got favourable or unfavourable prices for any duration of time because all price changes should ideally affect all customers at the same time. 

There is also a waterfall chart added to identify price changes for the selected products, but for the life of me I can't get the "billing date" data point to work on the category axis. I need the x-axis to go like "jan 22, Feb 22.." and so on, but i can't, and I need the waterfall to just show the month where the price change occured - not the whole series if there was a price change in just one month.

Any help on this would be greatly appreciated! Thanks!!

Regards,

Alvi

[Ritaf1983]

Hi @shoaibalvi 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[shoaibalvi · Accepted solution]

Hi @Ritaf1983 . I have updated the original question and have included a link to a PBIX file as well as anonymised source data. Hope it helps explain what I am trying to do!

I would greatly appreciate any help on this!