cancel
Showing results for
Did you mean:
Regular Visitor

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

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.

2 ACCEPTED SOLUTIONS
Solution Sage

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

Regular Visitor

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

3 REPLIES 3
Regular Visitor

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).

Regular Visitor

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

Solution Sage

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

Announcements

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

#### Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors