Adding an IF condition to a Measure

[cqueiroz2222]

Hello, I am trying to reply an Excel Rule into DAX Measure

The idea is:

When Result < 0, sum Result + Last Month C/C (superior line), otherwise, just reply the result
So just reply the result when C/C last month be positive

DAX MEASURE USED:

C/C  =

VAR CurrentDate = MAX('dim_Calendario'[Data])

RETURN

    SUMX(

        FILTER(

            ALL('dim_Calendario'),

            'dim_Calendario'[Data] <= CurrentDate

        ),

        [Result]

    )

Expected Result in excel and power bi version below:

[Anonymous]

Hi @cqueiroz2222 ,

I create a table as you mentioned.

Then I think you can create a calculated column.

C/C = 
VAR _Current = 'Table'[Result]
VAR _Previous =
    CALCULATE (
        SUM ( 'Table'[Result] ),
        FILTER ( 'Table', 'Table'[Month] < EARLIER ( 'Table'[Month] ) )
    )
RETURN
    _Current + COALESCE ( _Previous, 0 )

I also think you can create two measures and here are the DAX codes.

Result_Color = IF(MAX('Table'[Result])<0,"Red","Black")

C/C_Color = IF(MAX('Table'[C/C])<0,"Red","Black")

Next make them into Conditional formatting.

Finally you will get what you want.

Best Regards

Yilong Zhou

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

[cqueiroz2222]

But this last line isnt respecting the rule: if the last month result's be positive, just reply current month result

[Angith_Nair]

You can try the below dax:

C/C = 
VAR _Current = 'Table'[Result]
VAR _MaxMonth = MAXX(ALL('Table'), 'Table'[Month])
VAR _Previous =
    CALCULATE (
        SUM ( 'Table'[Result] ),
        FILTER ( 'Table', 'Table'[Month] < EARLIER ( 'Table'[Month] ) )
    )
RETURN
    IF (
        'Table'[Month] = _MaxMonth && _Current < 0,
        _Current,
        _Current + COALESCE ( _Previous, 0 )
    )

[cqueiroz2222]

Hello @Angith_Nair 

Thank you but it didnt work

[ryan_mayu]

did you mean if the value is <0, then the output is this month's value + last month's value?

[cqueiroz2222]

yes

if value < 0, then result + last month

if > 0, just reply the result

[FreemanZ]

hi @cqueiroz2222 ,

try like:

measure =

VAR _lm = EDATE(MAX('dim_Calendario'[Data]), -1)

VAR _lmcc =

SUMX(

        FILTER(

            ALL('dim_Calendario'),

            'dim_Calendario'[Data] <= _lm

        ),

        [Result]

    )

RETURN

IF([Result]<0, [Result] +_lm, [Result])

[cqueiroz2222]

Hello @FreemanZ 

Thank you but it didnt work