Calculate sum of projects that need investment for 3 years with different start date

[Amma17]

Hi all,

I have projects with different start date and projects investment takes for each 3 years and I need a Power BI measure that calculate the investment per year. I have only one table.

Thanks in advance for answer.

[barritown · Accepted solution]

Hello @Amma17,

Please consider the following solution consisting of 2 steps:

1) You transform your initial table into the new one with the following [DAX] code.

2) You add a matrix visual, set it up and enjoy the result. 

For convenience, here is the [DAX] code in plain text:

Table = ADDCOLUMNS (
             ( GENERATE ( SELECTCOLUMNS ( GENERATESERIES ( MIN ( data[Year] ), MAX ( data[Year] ) + 2 ), "Year", [Value] ),
                          VALUES ( data[Project] ) ) ),
            "Investment",
            VAR StartYear = YEAR ( LOOKUPVALUE ( data[Date], data[Project], [Project] ) )
            RETURN SWITCH ( TRUE (),
                            [Year] = StartYear, LOOKUPVALUE ( data[Y1], data[Project], [Project] ),
                            [Year] = StartYear + 1, LOOKUPVALUE ( data[Y2], data[Project], [Project] ),
                            [Year] = StartYear + 2, LOOKUPVALUE ( data[Y3], data[Project], [Project] ),
                            0 )
)      

  Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

[Amma17]

Thanks for your response.
I have another question:
How can I create another table with Investment, but split between investment proposal ( created with :
T_N = ADDCOLUMNS (

( GENERATE ( SELECTCOLUMNS ( GENERATESERIES ( MIN ( P_N[start date].[Year] ), MAX ( P_N[start date].[Year] ) + 2 ), "Year", [Value] ),

VALUES ( P_N[Column1] ) ) ),

"Investment propose",

VAR StartYear = YEAR ( LOOKUPVALUE ( P_N[start date], P_N[Column1], [Column1] ) )

RETURN SWITCH ( TRUE (),

[Year] = StartYear, LOOKUPVALUE ( P_N[Y1] ,P_N[Column1], [Column1] ),

[Year] = StartYear + 1, LOOKUPVALUE ( P_N[Y1] ,P_N[Column1], [Column1] ),

[Year] = StartYear + 2, LOOKUPVALUE (P_N[Y1] ,P_N[Column1], [Column1] ),

0 )

) and next to him investment realized, follow the same rule as investment proposal.

2.How can I fix investment proposal in the picture below to show me the real investment not the accumulated investment
in each row(third table).

Thanks in advance

[Amma17 · Accepted solution]

Solve it, I created a new table that contain years and I created a relationship one to one by year, so problem was solved.

[barritown · Accepted solution]

Hello @Amma17,

Please consider the following solution consisting of 2 steps:

1) You transform your initial table into the new one with the following [DAX] code.

2) You add a matrix visual, set it up and enjoy the result. 

For convenience, here is the [DAX] code in plain text:

Table = ADDCOLUMNS (
             ( GENERATE ( SELECTCOLUMNS ( GENERATESERIES ( MIN ( data[Year] ), MAX ( data[Year] ) + 2 ), "Year", [Value] ),
                          VALUES ( data[Project] ) ) ),
            "Investment",
            VAR StartYear = YEAR ( LOOKUPVALUE ( data[Date], data[Project], [Project] ) )
            RETURN SWITCH ( TRUE (),
                            [Year] = StartYear, LOOKUPVALUE ( data[Y1], data[Project], [Project] ),
                            [Year] = StartYear + 1, LOOKUPVALUE ( data[Y2], data[Project], [Project] ),
                            [Year] = StartYear + 2, LOOKUPVALUE ( data[Y3], data[Project], [Project] ),
                            0 )
)      

  Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian