Offer History Chart

Jordan_Davis1 · 02-22-2021 12:27 PM

I have a table of offers for ongoing negotiations showing ‘offers’ (my company’s offer to the client) and counteroffers (the client’s offer to us). This is how the data is recorded.

I’d like to make a chart like the one below to show the offer history with separate lines for offers and counteroffers. Is that possible?

Suvrutt_Gurjar

If you could slightly restructure the data format as below, then I believe you could plot the desired chart.

Chart

Jordan_Davis1

I’ve tried this, however, the offers aren’t made on the same day which means that the data will look like the image below and the charts will count all blank values as 0. In this case, I’d need to have a workflow for forward filling all of the blank amounts to be the latest amount and I can’t figure out how to do that.

Suvrutt_Gurjar

Oh okay, got it, thank you.
Depending on how the user makes a data entry in the app, there could be different approaches,

If the user makes the entry through a form, I believe you could even have an initial value for the [Our Offer] and [Their Offer] columns something like below. This initial value will capture the latest previous value and retain it if there is no fresh entry for either the offer or counteroffer on a day.

The initial value expression can be something like below for [Our Offer]

ANY(SELECT(Table Name[Our Offer], [Offer Date]=MAX(SELECT(Table Name[Offer Date], AND([Offer Date] <[_THISROW].[Offer Date], ISNOTBLANK([Our Offer]))))))

An exact similar one can be there for [Their Offer]

The expression may need change. Currently, it assumes one offer and counteroffer entry per day.

As it is evident, this will be a sync time expensive expression. As per my understanding, since it is for the initial value, it could be okay. Also, such requirements to find different row data points in a table could be expensive.

Jordan_Davis1

This is really close but I also have to tie it to the Lead ID, since there are offers for many different Leads on this table. I’ve used your expression with the following addition

ANY(SELECT(Table[Our Offer],[Lead ID] = [_THISROW].[Lead ID],
[Offer Date]=MAX(SELECT(Table[Offer Date],
[Offer Date] <[_THISROW].[Offer Date],
)))))

It’s adding in the earliest offer, not the latest. In other words, it seems to be ignoring the MAX part of the expression. Below you can see the values that are being added by the expression, in bold.

Suvrutt_Gurjar

Hi @Jordan_Davis1 ,

After you have added [Lead ID], please AND() , the two conditions of the SELECT() statement. In the absence of AND() , the MAX() relared condition is ignored.

Suvrutt_Gurjar

So please modify this to something like below

ANY(SELECT(Table Name[Our Offer], AND( [Lead ID] = [_THISROW].[Lead ID], [Offer Date]=MAX(SELECT(Table Name[Offer Date], AND([Offer Date] <[_THISROW].[Offer Date], ISNOTBLANK([Our Offer])))))))

Edit: Corrected an extra parenthesis in between inadvertently introduced.

Jordan_Davis1

Ha, yes you’re right, i’m not sure how i missed that. It’s still not working quite right but it’s much closer. I’ll have to play around with this until it starts working correctly. Thanks so much for your help!