How to find the Month between two dates

[Anonymous]

Hi Geniuses,

I have one issue , below is my data

Customer                              Start Date                              Term                    Amount

A	30/11/2016 20:00	36	1700000
B	15/06/2016 20:00	12	78000
C	31/08/2016 20:00	1	20000

I have a start date Term and Amount now what I want is three more columns in which if Start Date is 30/11/2016 and Term is 36 months then the one column will come as end date as 30/11/2019 and then in the other column this 36 months will come as Months(Nov-2016,Dec-2016,Jan-2017 and so on till 36 months) and then in one column the amount will get equally divided between the months and this goes for every client

As you can see the tenure is different and the start date is different  for every client

Thanks in advance,

S

[Gysbert_Wassenaar · Accepted solution]

Something like this:

LOAD

     Customer,

     AddMonths([Start Date], IterNo()-1) as [Start Date],

     AddMonths([Start Date], IterNo()) as [End Date]

     Term,

     Amount,

     Amount / Term as [Monthly Amount]

FROM

     source_data

WHILE

     IterNo() <= Term

     ;

[its_anandrjs]

Hi,

If in a script level you want to achieve this you can do this by using date functions (Monthend or date functions) or the for loop for creating the date data for the same.

Regards

Anand

[Anonymous]

You are awesome sir