Forum Discussion
Adding an IF condition to a Measure
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:
Expected Result in excel and power bi version below:
8 Replies
hi Anonymous ,
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])
did you mean if the value is <0, then the output is this month's value + last month's value?
Hi Anonymous ,
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.
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 ) )
