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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello marsclone 

Could you please share the updated data in which I'll get Net Income value? Please share the expected output as well. According to my understanding, you need to add Result and Net Income. Isn't it?

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

Hi Anonymous 

 

Ultimately, Net Income must be added to the Result in the balance sheet.

 

However, to accomplish this, the P&L must first be properly calculated. The current measurement ensures that the balance sheet is built up correctly, however the P&L is not.

 

The subtotals of the P&L must be determined differently. The desired result looks like this:

 

 

If the P&L is built properly, then the Net Income should be added to the result in the balance sheet. The desired result then looks like this:

 

 

I hope, you can help me with this. Thanks again for your help!

 

Regards,
Marcel

 

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

    Hi, marsclone 

     

    Could you please share the updated sample data? Otherwise we can hardly help you.

     

    Best Regards,
    Community Support Team _ Janey

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

      Hi, v-janeyg-msft 

       

      I am very grateful for all the help, but there is no updated data.
      The data as it is in the test file is the available data.

       

      As I mentioned in my post of 11-17-2021, the subtotals of the balance sheet are calculated correctly and those of the p&l are not.
      This should be solved first. I have illustrated by means of a picture what the desired result should be.

       

      I hope that you can help me with this?

      Kind regards,

      Marcel

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

        marsclone 

         

        Sorry, due to COVID-19 screening, I may not be able to give you an answer until next Wednesday.

         

        Best Regards,
        Community Support Team _ Janey