Forum Discussion

marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
4 years ago
Solved

Build Balance and P&L

Hi!   For our organization, with the help of fellow forum members, I have built a P&L and a Balance Sheet in recent years. However both are put together separately and in their own way.   The P&L...
Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    marsclone 

     

    I modify your final code:

    IF (
    MAX ( 'Report Balance'[Betreft] ) = "Balance",
    SWITCH (
        TRUE (),
        MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 1] ), [GROUP_1],
        MAX ( 'Report Balance'[Sortgroup] ) = MAX ( 'Report Balance'[Group 2] ),
            CALCULATE (
                SUMX ( ALLSELECTED ( 'Report Balance'[Sortgroup] ), [GROUP_1] ),
                FILTER (
                    ALL ( 'Report Balance' ),
                    'Report Balance'[Subtotal?] = 1
                        && 'Report Balance'[Sortgroup] < MAX ( 'Report Balance'[Sortgroup] )
                        && 'Report Balance'[Sortgroup] > Pre_Group2
                )
            ),
        [Total Balance]
    ),
    IF (
        MAX ( 'Report Balance'[Subtotal?] ) = 1,
        SUMX (
            FILTER (
                ALL ( 'Report Balance' ),
                [Betreft] = "P&L"
                    && [Sortgroup] < SELECTEDVALUE ( 'Report Balance'[Sortgroup] )
                    && [Subtotal?] = 0
            ),
            [Total Balance]
        ),
        [Total Balance]
    )
)

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please feel free to ask me.

     

    Best Regards,
    Community Support Team _ Janey

marsclone's avatar
marsclone
Icon for Helper IV rankHelper IV
4 years ago

Hi v-janeyg-msft 

 

Thanks for your response and effort put into it.

 

In the attached images I have tried to show the desired result. I have replicated these in Excel and have reversed the pluses and minuses here. So this does not match what is shown in Power BI.

 

In the images below I have now adjusted this. These are imitations in Excel that I would also like to see in Power BI.

 

As you indicate, first the logic of the measurement for the part of the P&L will have to be adjusted to arrive at the Net Income of -100,637.

 

When this is successful, this Net Income will have to be added to the Result in the balance sheet.

 

I hope that it is now a little clearer? Thank you very much for your help.

 

Kind regards,
Marcel

 

 

 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
4 years ago

marsclone 

 

I will modify the measure to display correct Net Income, It will take some time, please wait.

And I still can't understand:

Your measure doesn't affect the accumulated value because they are all 0. So I am puzzled how your expected result came out...

 

Best Regards,
Community Support Team _ Janey