Matrix with Start and End Date and A Number split over months

[moizsherwani]

Hi everyone,

So here are three sample rows

Name - Start Date - End Date - Amount

A - 1/1/2017 - 12/31/2017 - $120

B - 7/1/2016 - 1/1/2017 - $240

C - 7/1/2016 - 7/1/2017 - $360

So since A has a total spread of 12 months and an amount of $120 I need for the table to show $10 ($120/12 months) for each month (so $10 for Jan 2017, $10 for Feb 2017 till the end date of Dec 2017). For C since the spreadsheet is over 12 months and an amount of $360 I need the table to show $30 ($360/12 months) for each month so $30 for Jul 2016, $30 for Aug 2016 till the end date of Jul 2017). The eventual ouput would be something like

NAME - JAN 2016 - FEB 2016 - MAR 2016 - APR 2016 - MAY 2016 - JUN 2016 - JUL 2016 - AUG 2016 ... JUL 2017 - AUG 2017

A               $10              $10                $10           $10              $10              $10           $10           

B                                                                                                                                   $20            $20      ...

C                                                                                                                                   $30            $30     ...     $30

Thanks.

Moiz                                                              

[v-huizhn-msft]

Hi @moizsherwani,

For your given output table, the stare date is 2017/1/1 for name A, while it display in 2016 Jan, 2016 Feb,,, and so on. It's conflict, I try to reproduce, you can get amount of each month using the following forluma. But, we are unable to display like yours. If you want get the expected result, you must have a column field to display the Year-Month in your resurce table. And there is relationship betweem them.

result1 = Test[Amount]/IF(YEAR(Test[Start Date])=YEAR(Test[End Date]),MONTH(Test[End Date])-MONTH(Test[Start Date])+1,12-MONTH(Test[Start Date])+MONTH(Test[End Date]))

Best Regards,
Angelia

[moizsherwani]

Thanks for your reply, you are right I made a mistake with the table below it should indeed have been starting and ending in 2017 but I hope I was able to explain the concept (of what I need). The end result though needs to be a table and not only the calculation of the per month breakup.

[v-huizhn-msft]

Hi @moizsherwani,

Got it, I know you want to display your result in a table like your given screenshot. But we unable to get the result like yours afther test. Thanks for your understanding.

Best Regards,
Angelia