Can DAX Help me with pricing analysis? I need to check if resellers are using the new or old prices

[Tim91]

Hello ,

I am analyzing sales data for 2 resellers.

In 2022 we had multiple price increases and this leads us to different bonus requests.

By analyzing manually the data I noticed that they were not able to update the conditions for each endcustomer. 

Resellers provide us following infos

-invoice date

-total price

-Quantity sold and article number

-Endcustomer nr.

Based on the invoice date, Customer-nr. and Article-nr. I would like to vlookup in our price history source (xls file) in order to get the price for the period in question.

Once I have Our price/per Unit than compare it with the reseller price per unit (reseller Sales Value/ quantity sold).

If prices are the same than is perfect, if not I want to see if the price that they used is the old one.

to do so I need to get the value of previous contract nr 

customer nr /actual sequnce nr ---> customer nr /actual sequnce nr -1

here an example

4001/12 (valid from 01.01.22 to 30.06.22)

4001/13 (valid from 01.07.22 to 31.12.22)

so basicly if we have an invoice from 29.07.22 the contract in question will be 4001/13 so I would like to look after the price in previous contract 4001/12 to see if the price that they uses is the old one. (in the screenshot they used old price)

I tried this kind of things in Power Query, and it is easy to find the right contract, only issue is that when I try to merge all the values that I need it will take a lot of time. (I also buffered the table but still requires ca 5 extra minutes for each merge 28 Euro per Unit)

Can Power pivot with DAX be a solution? I have so far experience only with Power Query.

Thank you

Tim

[daXtreme]

Data munging is best done in Power Query, not in DAX. DAX is for (fast) calculations over a well structured model (can't stress this enough: well structured; think---star schema). The structuring takes place in Power Query.

[Tim91]

Thank you for answering.

I tried in power query, but using Merge multiple times on big data leads to huge loading times. (also used buffer table, that helps but still around 5 min each merge).