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 .

[geraldo]

@Jack0091 

An example workflow to handle only cases with slice days.
Just an idea

[Jack0091]

Hey !
Thank you for your idea, but it seems there is a situation where I have a slice of price that includes multiple months like this 

From_Date	To_Date	Price
09-01-2022	11-30-2022	0.623

How can i make this breaks into YearMonth as 202209,202210,202211 and each has QtdDays of 30 ?
Maybe generate rows tool in the bottom line after or before the formula that calculates YearMonth and QtdDays would be solution but I'm not familiar with  

And some months might contain more that one slice of price like follows :

From_Date	To_Date	Price
02-1-2022	02-06-2022	0.347
02-07-2022	02-28-2022	0.532

I have no idea how to solve this

[geraldo]

@Jack0091

First I made an adjustment to the sample you passed in 05 and 06 by changing the last day.

The [Count] variable is the days of the month.
Now you need to make an adjustment to check this second option that in the month can occur 2 values.
I'll take a look and send you the flow

[geraldo]

@Jack0091 

Check this workflow. I unified the calculations

[Jack0091]

Thank you for your help, its almost done ! but I have a problem what if the slice of 02-07-2022 until 02-28-2022 was like this 
02-07-2022 until 04-15-2022 it wont work sadly the days count would consider the whole slice regard the sample that i want to test

[geraldo]

@Jack0091

I may not have understood. Have you already analyzed the output of these fractionations and checked the formula is in yellow the step?
I'm basing it on the formula sample you put in the post.

If you can send a sample with all the situations and results it would help.

.

[Jack0091]

I have the last two columns in the test sample file where in the Calculated should be the consumption and in difference column the difference between the CONSUMPTION_PRICE and Calculated and it should be 0 difference

[geraldo]

@Jack0091 

I took a specific case with a large period but it did not reach the final value.
From an analysed.
Note: my excel date is western standard ddmmyyy maybe I have to adjust in the multi field formula

[Jack0091]

Thank you for your try, its not even close sadly