Get the most recent row (from date column) per Id, and count in a measure

[bd92]

Hello,

I have the following table (example):

Opportunity ID	From Stage	To Stage	Change date
1	New	Negotiation	11.04.2019
1	Negotiation	Offer Accepted	14.05.2019
1	Offer Accepted	Closed Won	14.06.2019
2	New	Negotiation	11.03.2019
2	Negotiation	Offer Accepted	02.05.2019
2	Offer Accepted	Closed Won	04.05.2019
3	New	Negotiation	11.05.2019
3	Negotiation	Offer Accepted	21.05.2019
3	Offer Accepted	Closed Won	23.05.2019

It's an extract from our CRM (Salesforce). This report has one line for each change done on an Opportunity record, logging the date. For example, my Opportunity ID 1 went from stage "New" to "Negociation" on 11.04.2019.

I have the following requirement: if I select a specific date, I want to have the number of opportunities that are on a specific stage, at that date.

For example, if I select 13.05.2019, it should return these rows:

Opportunity ID	From Stage	To Stage	Change date
1	New	Negotiation	11.04.2019
2	Offer Accepted	Closed Won	04.05.2019
3	New	Negotiation	11.05.2019

For each Opportunity ID, I take the row with the most recent date, but before my selected filter date.

Then, I would like to count the number of Opportunites per stage at the selected date. My final and wanted output is then:

Selected date is 13.05.2019:

• Negotiation = 2
• Closed Won = 1

Any idea on how I could write such a measure in Power BI?

Thanks in advance!

[v-yuta-msft]

@bd92 ,

I'm not very clear about your output table, isn't the result like below? 

Opportunity ID	From Stage	To Stage	Change date
1	Negotiation	Offer Accepted	11.04.2019
2	Offer Accepted	Closed Won	04.05.2019
3	New	Negotiation	11.05.2019

Could you explain about the logic about the "most recent date"?

Regards,

Jimmy Tao

[bd92]

Hello @v-yuta-msft ,

From a functional point of view, what I am trying to do is to reconstitute what my opportunities looked like in the past.

If I take the opportunity number 1, I see that today, the stage is "Closed Won", because the very last thing that happened for Opportunity 1 is to change from "Offer Accepted" to "Closed Won". 

But what if I want to know what was the stage of the same opportunity, a few months before?

Let say I want to know what was the stage at the end of April (30th April).

Power BI should look at all rows for opportunity ID 1, excludes the lines that have a change date after 30th April, and return the line with the highest Change date.

In this example, it would return the line with the Change date 11.04.2019 and then I can deduct that on 30th April, my opportunity 1 was at the Stage "Negotiation".

When I was mentioning "Most recent date", that means choosing the row with the most recent "Change date", per Opportunity ID.

@amitchandak , I checked the solution provided in the other thread, but that won't work with me: if I use power query to do that, I will lose a lot of flexibility when building my reports.

[amitchandak]

@bd92 

Please check this

https://community.powerbi.com/t5/Desktop/Recent-Value/m-p/701295#M338523

Also in the last few days, similar questions have been asked and the solution has been provided. Just try to search recent, last date. There are a few solutions. 

In between, I will try to check out more details.

[bd92]

@amitchandak I checked it, but doesn't work for me: in my case, I need that the measure returns something, whenever the period I select. In the example you gave me, when you select a period, it returns rows matching this period. If there is nothing at this period, it's blank.

In my case, it should look forthe closest value in time (going in the past) and return it.

[amitchandak · Accepted solution]

Dense rank on opportunity id and date and filter 1.

As Column == RANKX(Sales,
			Sales[Category] & Sales[Brand],
			,DESC,Dense
			
		)

[bd92]

Hello @amitchandak ,

I understand from your last answer that I need to create a calculated column.

I did it:

Column = RANKX(Changes;Changes[Opportunity ID] & Changes[Date of change];;DESC;Dense)

But the issue is that as a column, it's not dynamic. I need to apply filters directly on my report (with sliders for example), to select a date, and this won't update the calculated column. 

Or did I missunderstand, and that's a measure?

Thank you

[SnehaYadav]

how the issue got solved. Please share the idea.

[amitchandak]

Check this

https://community.powerbi.com/t5/Desktop/Create-Measure-for-finding-most-recent-value/td-p/633422