topic Re: Equally Distribute a Measure Across a Defined Number of Months in App Development

Equally Distribute a Measure Across a Defined Number of Months

jdean1012 — Mon, 20 Dec 2021 21:32:42 GMT

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!

Re: Equally Distribute a Measure Across a Defined Number of Months

Vegar — Mon, 02 Mar 2020 16:33:15 GMT

You can calculate months between dates with this expression

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

Re: Equally Distribute a Measure Across a Defined Number of Months

jdean1012 — Tue, 03 Mar 2020 14:28:52 GMT

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

Re: Equally Distribute a Measure Across a Defined Number of Months

jdean1012 — Tue, 03 Mar 2020 22:45:05 GMT

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