Struggling to break down values into yearly amounts. eg. Pipeline forecasting

[jakesolty]

Good day to you all,

I'm really struggling with trying to find the correct dax to use to break down a forecast pipeline into yearly amounts. We have multiple projects that start and end at different times. Please see below an example of how I need it to look in a table within Power Bi.

The columns without formatting is the information I already have and the blue shaded area is what I need to replicate.

Any advice would be much appreciated

Thanks, Jake

*Construction Length and Management Length are in months

[v-huizhn-msft · Accepted solution]

Hi @jakesolty,

I try to reproduce your scenario, please create the calculated column use the following formula.

2017 =
IF (
    YEAR (
        DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) )
    )
        = 2017,
    Test[Construction Length] * Test[Construction p/m],
    IF (
        YEAR ( Test[Start of Construction] ) = 2017,
        IF (
            (
                DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH )
                    <= Test[Construction Length]
            ),
            DATEDIFF ( Test[Start of Construction], DATE ( 2018, 1, 1 ), MONTH )
                * Test[Construction p/m],
            BLANK ()
        ),
        BLANK ()
    )
        + IF (
            YEAR ( Test[Start of Management] ) = 2017,
            IF (
                (
                    DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH )
                        <= Test[Management Length]
                ),
                DATEDIFF ( Test[Start of Management], DATE ( 2018, 1, 1 ), MONTH )
                    * Test[Management p/m],
                BLANK ()
            ),
            BLANK ()
        )
)

2018 =
IF (
    2018
        < YEAR (
            DATE ( YEAR ( Test[Start of Construction] ), MONTH ( Test[Start of Construction] ) + Test[Construction Length], DAY ( Test[Start of Construction] ) )
        ),
    IF (
        YEAR ( Test[Start of Construction] ) = 2017,
        IF (
            MONTH ( Test[Start of Construction] ) = 1,
            DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1,
            DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH )
        )
            * Test[Construction p/m]
            - Test[2017],
        IF (
            MONTH ( Test[Start of Construction] ) = 1,
            DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH ) + 1,
            DATEDIFF ( Test[Start of Construction], DATE ( 2018, 12, 31 ), MONTH )
        )
            * Test[Construction p/m]
    ),
    BLANK ()
)

For 2019 and 2019+, you can use the similar method to get. 

Best Regards,
Angelia

[jakesolty]

Thanks for the speedy response, I will check this now.