Performing operations on a table based on dates in rows in other table

[noncek]

Hello!

I am currently migrating an Excel-based solution into Power BI and have encountered a pickle I struggle to move past.

I have a table with order quotations for some products. Each product has some components, however their number differs from product to product (it can be between 1 and as many as 30 components per product). Also, each product has many different quotations, based on date and they can be on any date (this is not a case of a new batch of quotations for all products being updated e.g. every 1st January).

This table looks a bit like that (and has 200k+ rows altogether):

Procuct	Quote Date	Component	Comp. Cost
P1	2018-01-01	C1	10
P1	2018-01-01	C2	15
P1	2018-01-01	C3	10
P1	2019-01-01	C1	11
P1	2019-01-01	C2	13
P1	2019-01-01	C3	10
P2	2018-03-01	C4	2
P2	2018-03-01	C5	9
P2	2020-01-01	C4	3
P2	2020-01-01	C5	10
P3	2020-01-01	C6	100
P3	2020-01-15	C6	103
P4	2016-05-30	C1	8
P4	2016-05-30	C2	12
P4	2016-05-30	C7	4
P4	2019-01-01	C1	11
P4	2019-01-01	C2	13
P4	2019-01-01	C7	5
P5	2001-01-01	C8	10

So e.g. starting from 2018-01-01, the cost for P1 is 35 (10+15+10), and then from 2019-01-01 until now it is 34 (11+13+10).

For P2 the cost is 11 (2+9) between 2018-03-01 and 2020-01-01 and then rises to 13 (3+10). There was no quotation for P2 in 2019.

For P5 there is just one general quotation from 2001 and it doesn't change at all.

I also have table with volumes for each product looking like that:

Procuct	Month	Quantity
P1	2018-01-01	100
P1	2018-02-01	120
P1	2018-03-01	150
	…
P1	2020-05-01	140
P2	2018-01-01	35
P2	2018-02-01	40
P2	2018-03-01	30
	…
P2	2020-05-01	50
…

What I need to do is to get the spend on each product per month (and obviously have this analyzed further by years, suppliers, categories, etc.). I also need to see the spend on components themselves (as they can appear in more than one product).

I think I need to create some kind of function comparing quotation dates in one table to volume dates in the other, but struggle to even start on this 😕

Any hints, pointers and help greatly appreciated.

[MFelix]

Hi @noncek ,

What you need to create is a star schema model (https://docs.microsoft.com/en-us/power-bi/guidance/star-schema) so accordingly to you setup you need to have a calendar table and a products table.

Check link to create calendar table  (there are other based on power query also). Then you can make your calculations based on dates and also on products.

In attach PBIX file is a very simple presentation needs to be structure so the calculations give you the totals between periods, but please just check if the values for the specif dates are correct if yes I can seup the measures for the dates between periods.

Please tell me if this can help.

[noncek]

Hi @MFelix ,

thanks for your reply and help!

I did have calendar table and data schema already in my model, however I see it wasn't connected properly. I went with your proposal.

As for the pbix attached, it does show expected values for the periods when there are any quotations. If you could help with filling the gaps for periods between quotations, I'd be very grateful.

I can't figure out though, where do totals come from in this one?

Whereas total for specific year make sense, the totals for rows and columns boggle my mind...

Anyway, thanks again for your help!

[MFelix]

Hi @noncek ,

Just a few question that appear on the calculation I was making and can change the way we need to approach the problem:

• On product P3 you have Quotations you have the same component with two different dates is this correct?
• Can you have several quotations for different components on different dates within the same month if so what is the date that is consider?

Just to clarify and get the correct calculations.

[noncek]

Hi @MFelix 

For both your questions the answer is positive.

There can be cases, where a product has more than one quotation during a month. There can alos be a situation when in a cerain month there is a new quotation (or a couple of quotations) for only one of the components for the product, while the others don't have any new quotation (hence no change).

Generally the logic should be that to calculate the cost of the product (=sum of costs if its components) at any point in time, we should take the latest available quotation for each component.

So in the example product P3 costs 100 between 2020-01-01 and 2020-01-14 and from 2020-01-15 the cost changes to 103 (until new quotation appears for the component C6 - in this particular case it has only one component).

The volumes file is granular down just to months, so the model assumes volumes are coming always on the first day of the month. I didn't put the volumes for P3 in my example, but assuming they were 1000 pcs every month, the spend on P3 in Jan 2020 would be 100.000, Feb 2020 would be 103.000 and all following months at 103.000.

Hope this answers your question.

And once again thank you for your help and staying on the case!

[MFelix]

Hi @noncek ,

I'm sorry I didnt get back to you yet, however I am doing some trainig and is hard to get the time.

I will put you in touch with a great datanaut.

@parry2k  can you please fill on my behalf on this question? I have made a smal file but feel free to get a different and better solution.

[parry2k]

Thanks @MFelix  I can surely assist with this.

@noncek It will be much easier if you put sample data in excel file and share it here and also one sheet put the expected result, it will help me to create the solution in Power BI and send it back to you otherwise there will be lot of back and forth.

Cheers

P

[noncek]

Thanks @MFelix  for referral and thanks @parry2k for taking interest!

As a regular community member I don't seem to have an option to attach a file directly here, but please find a transfer link to the xls:

https://wetransfer.com/downloads/515315cc8889932359751c914a68084020200625113807/bcf2da8976af55f1aa91...

Have a great day!

K