Forum Discussion

dvhez's avatar
dvhez
Icon for Helper II rankHelper II
7 years ago
Solved

XNPV every 2 months

How can I calculate the XNPV of 2 months (from 01/2018 to 02/2018 for example) but for the next "block" calculate XNPV from 03/2018 to 04/2018)?

 

My data looks like this and I need to calculate the third column.

 

Date               Value               XNPV

01/2018             1                 XNPV_1

02/2018             2                 XNPV_1

 

03/2018             3                 XNPV_2

04/2018             4                 XNPV_2

 

05/2018             5                 XNPV_3

06/2018             6                 XNPV_3

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi there,

    If you would instead of XNPV_1, XNPV_2, etc. put in the actual values I am sure that I or someone else will answer you right away with a solution.

     

    Best regards,

    Kristjan

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi dvhez,

    Based on my test, you could refer to below formula:

    XNPV = CONCATENATE(("XNPV_"),ROUND([Value]/2,0))

    Result:

    You could also download the pbix file to have a view.

     

    Regards,

    Daniel He

     

  • v-danhe-msft's avatar
    v-danhe-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    Hi dvhez,

    Could you please tell me if your problem has been solved? If it is, could you please mark the helpful replies as Answered to close this topic?

     

    Regards,

    Daniel He