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 

Please check the attached file.

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

Hello Anonymous 

 

Thank you for your work, only i don't see a difference? Is there something going wrong?

 

  • 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