Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Amma17
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.

Amma17_0-1684401064695.png

Thanks in advance for answer.

2 ACCEPTED SOLUTIONS
barritown
Super User
Super User

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.

barritown_0-1684504363945.png

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

barritown_1-1684504770787.png

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

View solution in original post

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

View solution in original post

3 REPLIES 3
Amma17
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).

 

Amma17_0-1685034479459.png

Thanks in advance

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
Super User
Super User

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.

barritown_0-1684504363945.png

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

barritown_1-1684504770787.png

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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.