prorate calc

[BI_Dev]

Team - I have the below data table as an Ex.

ID	Exp	Start Date	End Date
1	10,000	3/31/2020	3/31/2023

Here - I have to pro-rate and divide the expense based on the start date and end date. I have do distribute expense based on expense and no of months in each year.Here we have 10,000 exp  and in 2020 we have 9 months and from 2021 - 2022 12 months in each year and in 2023 we have 3 months...so amount would distribute as below.

ID	Exp	Start Date	End Date	2020	2021	2022	2023
1	10,000	3/1/2020	3/31/2026	2493	3324	3324	831

all these years will total upto 10,000...is this doable ?

Thank you much.

[tm_burgers · Accepted solution]

I have to create something similar and have been avoiding it, seeing your question made me tackle it for the both of us:

[RawData]:
LOAD * INLINE 
[
ID;Exp;Start Date;End Date
1;10000;3/1/2020;3/31/2026
2;15000;5/1/2021;5/31/2028
](delimiter is ';');


left join (RawData)
LOAD
ID,
Interval([End Date]-[Start Date],'d')+1 as TotalDays,
(Exp)/(Interval([End Date]-[Start Date],'d')+1)	as Exp_PerDay
Resident RawData;

MinMaxYear:
LOAD 
year(min(date([Start Date]))) as MinYear,
year(min(date([End Date]))) as MaxYear
Resident [RawData];


for year=peek('MinYear',0,'MinMaxYear') to peek('MaxYear',0,'MinMaxYear')

left join (RawData)
LOAD
ID,

if(year([Start Date])>$(year),0,

if(year([Start Date])<$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-MakeDate($(year),1,1),'d')+1)*Exp_PerDay,

if(year([Start Date])=$(year) and year([End Date])>$(year),(interval(MakeDate($(year),12,31)-[Start Date],'d')+1)*Exp_PerDay,

if(year([Start Date])<$(year) and year([End Date])=$(year),(interval([End Date]-MakeDate($(year),1,1),'d')+1)*Exp_PerDay

))))as $(year) 
Resident RawData;

next

[BI_Dev]

This is awesome...is there a way to group all years and add Year as a filter or dimension ?

[tm_burgers]

Using the above Script will create a Dimension for each year from your Min Start Year to your Max End Year.

You could then crosstable this into a new table.... which may be the best for Data Analysis

so you would have a new table with the EXP by YEAR:

 ID           YEAR      EXP

1              2020       2493

1              2021       3324

1              2022       3324

1              2023       831