Generating monthly table with diminishing Object values on hardcoded predefined values

[Muffin92]

Hello all, 

My data contains alot of different object with yearly or monthly "contracts". These contracts represent the values of object on specific dates and whether the value deminishes on a periodic (yearly) or monthly basis. Example data:

For example: object 1 is worth 52.000 on 1-7-2020. On 1-8-2020 the value is the monthly depreciation between 52k and 41k. The problem is that the depreciation isn't the same during the start (1-7-2020) and the end date (1-7-2022). For the yearly contracts it's somewhat different. The value for object 2 on 1-1-2021 is 100K. A day later, 2-1-2021 it's 70K untill 1-1-2022. I'm having trouble generating a new table with values on a monthly basis. The output would be something like:

Could someone please help me out? Many many thanks beforehand.

Kind regards, Muffin

[AntonioHR]

Hello @Muffin92,

I think you could make a new table just with OBJECT and MIN(DATE) so you have the starting date. 

Then in your main table add a column which will use DATEDIFF(DATE, DATE(NEWTABLE)) and an IF(type contract) to get the value 

[amitchandak]

@Muffin92 , I did not get complete information. But this approach to create a new table can help

addcolumns(generate(Table, generateseries(0,4,1)), ("New Date" , Eomonth([Date], if([COntract] ="Monthly",([Value]*1)-1, ([Value]*12)-1) )+1) )

generateseries will decide the number of year or month 

[Muffin92]

Thanks for the reply! Sadly i'm not able to get the output i'm looking for (screenshot 2 in post) with a value split between months since it's based on a single value. I can't make it work for multiple values (like object 1) or when the contract is different (yearly). Do you have some additional tips that could work with that?

Kind Regards, Muffin