Forum Discussion

ruinaldo's avatar
ruinaldo
Helper II
8 years ago

Segregate by month Positive and Negative Client Balance

 

I have a list of clients movemented by month. Every month i need to segregate the accumulated values (positive versus negative clients balance) by columns. If the balance movements by client is positive so i write in "Positive Column", otherwise, if the balance movements is negative i write in "Negative Column".

Can you help me please? 

 

 

Need Help

6 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    Hi ruinaldo,

     

    From above image, the left table is source table, and the right one is your desired result, right?

     

    Please create a calculated table like this:

    Positive & Negative =
ADDCOLUMNS (
    CROSSJOIN (
        VALUES ( 'Client Balance'[Month] ),
        VALUES ( 'Client Balance'[Number Client] )
    ),
    "Movement", LOOKUPVALUE (
        'Client Balance'[Movenments of month],
        'Client Balance'[Month], [Month],
        'Client Balance'[Number Client], [Number Client]
    )
)

    And add below calculated columns:

    accumulated values =
CALCULATE (
    SUM ( 'Positive & Negative'[Movement] ),
    FILTER (
        ALLEXCEPT ( 'Positive & Negative', 'Positive & Negative'[Number Client] ),
        'Positive & Negative'[Month] <= EARLIER ( 'Positive & Negative'[Month] )
    )
)
    + 0

Positive =
IF (
    'Positive & Negative'[accumulated values] >= 0,
    'Positive & Negative'[accumulated values],
    0
)

Negative =
IF (
    'Positive & Negative'[accumulated values] <= 0,
    'Positive & Negative'[accumulated values],
    0
)

     

    To get your desired output, refer to below formula:

    Result Table =
SELECTCOLUMNS (
    'Positive & Negative',
    "Month", "Month" & " "
        & 'Positive & Negative'[Month],
    "Number Client", 'Positive & Negative'[Number Client],
    "Positive", 'Positive & Negative'[Positive],
    "Negative", 'Positive & Negative'[Negative]
)

     

    Best regards,

    Yuliana Gu

    • ruinaldo's avatar
      ruinaldo
      Helper II
      8 years ago

      Thank you very much for your reply Yuliana.

      But I have the following error message:

       

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi ruinaldo,

         

        Please try the solution in my original post in Power BI desktop (rather than in Power Pivot) to see whether it works for your data table. Please note that the first and the third formula in my post is used to created a calculated table, not a calculated column. As I know, the DAX supported by Power BI also applies to Power Pivot. If above DAX can return expected result in desktop in your scenario, for how to new a calculated table and how to run DAX correctly in Power Pivot, please post questions in Power Pivot forum to get more helpful answer.

         

        Best regards,

        Yuliana Gu