Date a value changed from -03 to -01

[jutu]

Hi. I hope that you can help with the below and much appreciated in advance.
My company sells a product through both Accountant and Direct to End users. When the product is sold through Accountants it has an accounting code that end -03. When it's sold directly its ends -01. Sometimes a subscription will "delink" from an Accountant and move to Direct. When this happens we see the Accounting Code for a subscription move from ending -03 to -01 on a particular subscription version. I need a report to show all the subscription references that move from Accountant to Direct with the change date of the version they moved.

What I am looking for on a subscription reference is the version when the Accounting Code changed from ending -03 to -01. From that version we need the subscription changed date from -03 to -01.
Final result would look something like in the attached.

Suggested ways to me;
- Merging data (still no permission so this option is not relevant to me)
- Dax Offset function (new to September version)
- Power Query List. Range function

I need to collect all the ones that went from accountant (03) to direct (01), to show when each of them have changed and what date. The accountant and direct tables are separate and I think have to be merged/joined but I don't have permission yet so that is out of the picture.

 

[p45cal]

In the attached a Power Query offering at cell F1. It's more long-winded than it needs to be.
There are 2 groups of queries, one called Other Queries which are the working ones, and another group called NotNeeded which is only there to show the steps of the function in the other group.
Your workbook contained scant example data so I haven't bothered to test it thoroughly.

 

[jutu]

Hi. Sorry for late reply but didn't see you had come back to me. Apologies but can't explain it much better and the workbook doesn't contain much data but I thought that would be enough to kind of understand what info I would need to extract from it. From my workbook what I would need to extract is the line ending -01 when it switches to direct.

So, the result from my workbook would be the record with the end -01
I hope it helps and apologies if not very clear

Subscription Reference	Subscription Version	AccountingCode	Change Date
A-SXXXXX678	1​	xxxxx-xxxxx-xxxx-03	01/02/22​

 

[p45cal]

This is perverse.
Below is a picture of your source data, sorted by Change Date, oldest at the top, most recent at the bottom.

You want to know when it changes from -03 to -01. That clearly happens at the last line (the most recent) on 1st Oct 2022, Subscription Version 4.
Why on earth would you want to see 1st Feb, Sub. Version 1 when no change took place around that time?!

 

[jutu]

There are 2 type of versions mainly; accountant (03) and direct (01).
The ones that I would need are only the direct versions if that explains it.
You see, clients change from account version to direct version and need to know when that switch happened.
Again, sorry if my explanations are poor or just rubbish

 

[jutu]

subscription version 1 is when the switch happened, and that's what I need to extract

 

[p45cal]

In your msg #3 above:

So, the result from my workbook would be the record with the end -01

Then you show a line with a record ending -03 !?

FYI, my result shows -01 record:

Either I'm bonkers or you are.

 

[jutu]

hahaha. It sure is me. Sorry if am loosing your patience here as well as loosing your time.

Yes you right. I was mixing the account type with the end of the accounting code. Those are the records that i need to get with ending code -1

 

[jutu]

I was suggested to use RANK or OFFSET......or both?

 

[p45cal]

I was mixing the account type with the end of the accounting code.

I don't see account type anywhere.

I was suggested to use RANK or OFFSET......or both?

Aren't we all good to go?
Why introduce RANK and OFFSET? If we're moving on to how to bring the two data tables together, I'd need to catch sight of both tables

- Merging data (still no permission so this option is not relevant to me)

Merging data doesn't affect the source data, it only happens in memory while the query executes, so I don't see why you need permission.
If I can see these two tables, I can tweak the query to use those instead of your sample merged data - you won't even see a table with merged data. Attach a workbook with both tables in, or if they're in another form, such as csv files, attach those instead.