Date slices need help

[Jack0091]

I have two data sets the first is like this 

Previous_Reading	Current_Reading	Quantity	Multi_Slice	No_Of_Days
04-01-2022	04-30-2022	350	No	30
05-29-2022	07-02-2022	600	Yes	33

The second data set is like this :

From_Date	To_Date	Price
04-01-2022	04-30-2022	0.356
05-01-2022	05-30-2022	0.246
06-01-2022	06-31-2022	0.435
07-01-2022	08-1-2022	0.364

What I want is a column in the first table that calculates the consumption as follow :

for the first case we have a slice price that does not interrupt more than one slice it only exists in the period from 04-01-2022 until 04-30-2022 with the price of 0.356 so the calculation would be (quantity * price ) which is (350*0.356) 

for the second case my reading has crossed multiple slices from the second data set which will complicate things where i have to calculate how many days were inside each of the slices as a portion of total number of days for the reading for example if we take the second row from the first data set to apply this it would be like this (((portion of the days)/total days )* Quantity * slice price) + (((portion of the days)/total days )* Quantity * slice price) +(((portion of the days)/total days )* Quantity * slice price)

which would translate into (((1/33) * 600 * 0.246) + ((30/33) * 600 * 0.435) + ((2/33) * 600 * 0.364))

I hope someone could help with this, thanks in advance .

[Pang_Hee_Choy · Accepted solution]

hmm. better split to date then calculate later. 

i split to date but exclude last reading date, then join with slice date and do the calculation. the result has near to zero beside 1 line.

[Jack0091]

Thank you so much !
It worked

[geraldo · Accepted solution]

@Jack0091 

As much as you already considered it solved, I added the flow and hit them, the results were ok.
Good luck

[geraldo]

@Jack0091 

At the price base you passed on line 106, the values ??start again. some errors were due to this data continuity

[Jack0091]

Works thank you so much !

[Jack0091]

True I made a mistake preparing a sample for test it shouldnt be repeated, thank you for notice !