Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
My data has:
ProjectID
Start Date
End Date
Amount
I'd like to distribute the amount equally across the number of months between the Start Date and the End Date.
I think the way to do this is to create a row in the load script per ProjectID, per month (between the dates) and divide the amount by the number of months and assign to each row. But, I am not entirely sure how to do this.
Any help would be greatly appreciated!
This seems to work:
[Table2]:
NoConcatenate
Load
[ProjectID],
Date(MonthStart([Start Date], IterNo() - 1)) as [Month]
While
MonthStart([Start Date], IterNo() - 1) <= [End Date];
Load
[ProjectID],
Date(MonthStart([Start Date])) as [Start Date],
Date(MonthStart([End Date])) as [End Date]
Resident [Table1];
You can calculate months between dates with this expression
((year(enddate)*12)+month(enddate)) - (((year([startdate])*12)+month([startdate]))) as NoOfMonths
Thanks Vegar! Determining the number of months between the two dates really isn't my problem. I need to be able to distribute the Amount equally across the months, which I assume would involve creating distinct rows per ProjectID/Month.
The desired visual, for example, would be something line a line graph with months on the x-axis and the distributed amount as the measure.
THANKS
This seems to work:
[Table2]:
NoConcatenate
Load
[ProjectID],
Date(MonthStart([Start Date], IterNo() - 1)) as [Month]
While
MonthStart([Start Date], IterNo() - 1) <= [End Date];
Load
[ProjectID],
Date(MonthStart([Start Date])) as [Start Date],
Date(MonthStart([End Date])) as [End Date]
Resident [Table1];