Nested IF statement in custom column

[gianmarco]

Dear All,

I'm trying to create a custom column as follows:

var SUPPLIERS = CALCULATE(SUM(Cashflow[Importo (+/-)]),Cashflow[Source]=0,VALUES(Cashflow[Categoria])="FORNITORI")

var PREVISIONE_FORNITORI_CURRENT = Cashflow[Source]=5 && Cashflow[Completed_month]=0 && Cashflow[Par1]=1

var CLIENTS = CALCULATE(SUM(Cashflow[Importo (+/-)]),Cashflow[Source]=0 && VALUES(Cashflow[Categoria])="CLIENTI")

var PREVISIONE_CLIENTI_CURRENT = Cashflow[Source]=5 && Cashflow[Completed_month]=0 && Cashflow[Par1]=0

var PREVISIONALE_PAST = Cashflow[Source]=5 && Cashflow[Completed_month]=1

RETURN

IF( PREVISIONALE_PAST, 0, IF(PREVISIONE_FORNITORI_CURRENT, Cashflow[Importo (+/-)] - (SUPPLIERS), IF(PREVISIONE_CLIENTI_CURRENT, Cashflow[Importo (+/-)] - (CLIENTS), Cashflow[Importo (+/-)])))

but it returns me the error: "Expression True/False does not specify a column ..."

Would you advise me how can I adjust this?

Thank you in advance

gianmarco

[gianmarco]

Thank you a lot, error is gone! 

Problem is: it doesn't work the following in red:

RETURN

IF( PREVISIONALE_PAST, 0, IF(PREVISIONE_FORNITORI_CURRENT, Cashflow[Importo (+/-)] - SUPPLIERS, IF(PREVISIONE_CLIENTI_CURRENT, Cashflow[Importo (+/-)] - CLIENTS, Cashflow[Importo (+/-)])))

It doesn't subtract anything 😞

[Anonymous]

Hi @gianmarco ,

Please try code as below.

Column =

VAR SUPPLIERS =
    CALCULATE (
        SUM ( Cashflow[Importo (+/-)] ),
        FILTER ( Cashflow, Cashflow[Source] = 0 && Cashflow[Categoria] = "FORNITORI" )
    )
VAR PREVISIONE_FORNITORI_CURRENT = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 0
    && Cashflow[Par1] = 1
VAR CLIENTS =
    CALCULATE (
        SUM ( Cashflow[Importo (+/-)] ),
        FILTER ( Cashflow, Cashflow[Source] = 0 && Cashflow[Categoria] = "CLIENTI" )
    )
VAR PREVISIONE_CLIENTI_CURRENT = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 0
    && Cashflow[Par1] = 0
VAR PREVISIONALE_PAST = Cashflow[Source] = 5
    && Cashflow[Completed_month] = 1
RETURN
    IF (
        PREVISIONALE_PAST,
        0,
        IF (
            PREVISIONE_FORNITORI_CURRENT,
            Cashflow[Importo (+/-)] - ( SUPPLIERS ),
            IF (
                PREVISIONE_CLIENTI_CURRENT,
                Cashflow[Importo (+/-)] - ( CLIENTS ),
                Cashflow[Importo (+/-)]
            )
        )
    )

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[gianmarco]

It seems not working properly. Again, it doesn't subtract anything 😞

[ppm1]

This pattern is likely the issue. I see it in two places. Also, are you sure you want to create a column for this? Which table are you putting it on? A measure may be better.

VALUES ( Cashflow[Categoria] ) = "FORNITORI"

Try just 

Cashflow[Categoria]  = "FORNITORI"

Pat