Segregate by month Positive and Negative Client Balance

[ruinaldo]

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? 

 

[v-yulgu-msft]

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]

Thank you very much for your reply Yuliana.

But I have the following error message:

[ruinaldo]

I have a problem.......in left table i insert a new row "211000500" "Month1" "300".

In this case i have a number of client that repeats in the same month.

First when i insert a new table give me a error"a table of multiple values was supplied where a single was expected"

Second if i insert a column with accumulated formula (without insert a new table) the result of client 211000500 repeats in the Month1. So when i insert a matrix table the result was duplicated. How can i resolve this? 

[v-yulgu-msft]

Hi @ruinaldo,

In that case, before creating the calculated table 'Positive & Negative', please create a summarized table like below:

summarize balance table =
SUMMARIZE (
    'Client Balance',
    'Client Balance'[Number Client],
    'Client Balance'[Month],
    "Movements", SUM ( 'Client Balance'[Movements of Month] )
)

Then, the rest steps are the same based on above summarized table.

Regards,

Yuliana Gu

[ruinaldo]

Hi,

I have a table of clients movements by month. I don´t need the sum of positive or negative values

I need by month the accumulated values by client. And each month i have client balance positive others negative that i want separate in diferent columns.

Month 1 = positive (650) = acumulated balance of clients 211000500 (500) and 211000700 (150)

Month 2  = positive (350) = acumulated balance of clients 211000500 (200) and 211000700 (150). 

                    negative = acumulated balance of client 211000600 (-100)

Thanks for your help.

[v-yulgu-msft]

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