Equally Distribute a Measure Across a Defined Number of Months

[jdean1012]

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!

[Vegar]

You can calculate months between dates with this expression

((year(enddate)*12)+month(enddate)) - (((year([startdate])*12)+month([startdate]))) as NoOfMonths 

[jdean1012]

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

[jdean1012 · Accepted solution]

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];