Forum Discussion

marsclone's avatar
marsclone
Helper 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...
  • 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