Cumulative Monthly From Yearly Cost

[sirisadashiva]

Hello,

I am new to DAX and Power BI. I have been struggling with this for a couple of weeks. I would be grateful for any help on the cost report I'm currently working on.

I have a Table with columns:

Id   StartDate      EndDate      AnnualCost 

1    02/10/2010   02/10/2011   120,000

2    04/3/2010     04/03/2011   100,000

3    03/14/2011   03/14/2012   200,000

I also have a separate Calendar Table.

I need to calculate cumulative monthly totals from this.

Initially I added Month dates extracted from start and end date fields and calculated annual cost per month like so:

Id   StartDate      EndDate      AnnualCost  MonthEndDate AnnualCostPerMonth

1    02/10/2010   02/10/2011   120,000      03/09/2010          10,000

1    02/10/2010   02/10/2011   120,000      04/09/2010          10,000

......

2    04/3/2010     04/03/2011   100,000      03/02/2011          8333.33

.....

3    03/14/2011   03/14/2012   200,000      04/13/2011          1666.666

....... till EndDate

I then connected the Table [MonthEndDate] to Calendar table and calculated the cumulative with the measure: 

Cumulative  = CALCULATE(SUM(Table[AnnualCostPerMonth]),

FILTER(ALLSELECTED(Table),

Table[MonthEndDate] <= MAX('Calendar'[Date])))

This did give me the right answer, however it is expanding the Table to thousands of rows when I add the MonthEndDate column to the Table. How do I avoid this? 

(Essentially, the Id fields should not be repeated, so I need to avoid splitting the start-end date into monthly dates) 

[amitchandak]

@sirisadashiva , In case you need allocation, check how monthly has been created

https://www.dropbox.com/s/fnq82ksdzk1lqs3/Target_allocation_daily.pbix?dl=0

or

https://www.dropbox.com/s/yuv64v0cneseghx/value%20Split%20between%20months%20start%20end%20date.pbix...

[sirisadashiva]

Thank you, I looked at those, it causes the Id fields to be repeated which I'm trying to avoid.