Dax measure with condition if

[cqueiroz2222]

Hello, can you help me on how do I transfor an excel formula into a DAX Measure?

when my last month's result be positive (dec/23 =64.228)
next (jan/24 = 138.023) C/C measure must repeat the Result

In excel it would be:
=SE(E20<0;(D21+E20);D21)
E20 = previous month (superior line)
D21 = [Result]

[vojtechsima]

@cqueiroz2222 
is this something you want (you need calendar for this).

cc = 
var previousMonthValue = 
CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH)
)
var check = IF( previousMonthValue >= 0, [valuemeasure], previousMonthValue + [valuemeasure])

return check

[cqueiroz2222]

Hello @vojtechsima 

This dax measurement worked, but the previous calculation is going wrong

The central idea of ​​the measure is:

Perform a quantitative sum of the results

• When negative = add to the previous result
• When positive = repeat the [Result]

I share some of the data to help

Note that the account that is being wrong is: (-29.813) + (-67.166) = -96.979 and not -96.229 as show in the table below:

[DataNinja777]

Hi again, @cqueiroz2222 ,

To achieve this behavior in DAX, where the cumulative measure repeats the Result for the current month if the previous month's result is positive, we need to account for DAX's non-recursive nature. While we can't directly reference the result of the previous row (as in Excel), we can work around it with some conditional logic. Here's an approach that can approximate your requirement:

Cumulative Total with Conditional Repeat = 
VAR CurrentDate = MAX('Calendar'[Date])
VAR PreviousMonth = PREVIOUSMONTH(CurrentDate)
VAR PreviousMonthResult = 
    CALCULATE(
        [Result],
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] = PreviousMonth
        )
    )
RETURN
    IF(
        PreviousMonthResult >= 0,
        [Result],
        SUMX(
            FILTER(
                ALL('Calendar'),
                'Calendar'[Date] <= CurrentDate
            ),
            [Result]
        )
    )

This DAX formula provides a way to "repeat" the Result value when the previous month’s result is positive. However, remember that DAX doesn’t natively support recursive calculations that rely on the outcome of the previous calculation within the same measure. Instead, this approach checks the previous month’s Result conditionally and applies the cumulative calculation if necessary.

Best regards,

[rajendraongole1]

Hi @cqueiroz2222 - as per above excel formuale if the previous month’s result is positive, you can use a measure that checks the value of the previous month. 

C_C_Measure =
VAR PreviousMonthResult =
CALCULATE([Result],
DATEADD('YourDateTable'[Date], -1, MONTH)
)

RETURN
IF(
PreviousMonthResult < 0,
[Result] + PreviousMonthResult,
[Result]
)

you can replace Result with actual measure that you created for monthly result value.

still issue exist, please share some sample data for reference , will check at our end.

[cqueiroz2222]

This dax measurement worked, but the previous calculation is going wrong

The central idea of ​​the measure is:

Perform a quantitative sum of the results

• When negative = add to the previous result
• When positive = repeat the [Result]

I share some of the data to help

Note that the account that is being wrong is: (-29.813) + (-67.166) = -96.979 and not -96.229 as show in the table below:

[vojtechsima]

Hello, @cqueiroz2222 ,
I refactored my case:

cc = 
var previousMonthValue = 
CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH)
)

var currentValue = TOTALMTD([valuemeasure], 'calendar'[Date])

var togetherValue = 
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date]) && DATEDIFF('calendar'[Date], MAX('calendar'[Date]), MONTH)<=1
)
var cumulative = 
CALCULATE(
    IF( previousMonthValue >= 0, [valuemeasure], togetherValue),
    DATESINPERIOD('calendar'[Date], MAX('Table'[date]), -1, MONTH)
)
var check = IF( previousMonthValue >= 0, [valuemeasure], previousMonthValue + [valuemeasure])

return cumulative

It would help me if you could show me how the result should look like in your data that you shared.

[cqueiroz2222]

@vojtechsima 

Apparently the measure “works”, but why is the result wrong?

Please see below:

(-96.979)  + (-64.354) = -161.333 and not -16.583, can you verify it?

I'am trying to reply this:

[vojtechsima]

Hey @cqueiroz2222 

I would be interested how the JUly or August look really, after the + value.

cc = 
var previousMonthValue = 
CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH)
)

var currentValue = TOTALMTD([valuemeasure], 'calendar'[Date])

var togetherValue = 
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date]) && DATEDIFF('calendar'[Date], MAX('calendar'[Date]), MONTH)<=3
)
var cumulative = 
CALCULATE(
    IF( previousMonthValue >= 0, [valuemeasure], togetherValue),
    DATESINPERIOD('calendar'[Date], MAX('Table'[date]), -1, MONTH)
)
var check = IF( previousMonthValue >= 0, [valuemeasure], previousMonthValue + [valuemeasure])

return cumulative

I am playing with the 17th row condition the <=3

[cqueiroz2222]

Hello, @vojtechsima 

Thank you for the adjustments.
Now it starts to get wrong in jul/2023

Please see the original data until the end:

[vojtechsima]

@cqueiroz2222 

cc = 
var previousMonthValue = 
CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH)
)

var currentValue = TOTALMTD([valuemeasure], 'calendar'[Date])

var togetherValue = 
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date]) //&& DATEDIFF('calendar'[Date], MAX('calendar'[Date]), MONTH)
)

var stopValue = 
CALCULATE(
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date])),
 DATESMTD('calendar'[Date])
)

var cumulative = 
CALCULATE(
    IF( previousMonthValue >= 0, stopValue, togetherValue),
    DATESINPERIOD('calendar'[Date], MAX('Table'[date]), -1, MONTH)
)
var check = IF( previousMonthValue >= 0, [valuemeasure], previousMonthValue + [valuemeasure])

return cumulative

At this point, I am just adding layers, I suggest refactoring it later for optimisation.

[cqueiroz2222]

@vojtechsima 

At a certain point the measurement starts to miscalculate, is there a limiter?

Is there any way to not put an "end" as I will have the results for the next few months?

I want to maintain only the logic, regardless of the period

[vojtechsima]

@cqueiroz2222 do you reset it every year? because it looks like so, based on the calculation it should go further.
because Why do you repeat value in January24 but not in Nov23 or Set23?

[cqueiroz2222]

The idea is to calculate based on the [Result] of each month, regardless of the year

The rule is:

When last month is < 0,

calculate the previous month + [Result] of the current month

In other words, it will only repeat the month's result [Result] when the previous result is positive

[vojtechsima · Accepted solution]

I get it now @cqueiroz2222 

cc = 

var currentValue = TOTALMTD([valuemeasure], 'calendar'[Date])

var togetherValue = 
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date]) //&& DATEDIFF('calendar'[Date], MAX('calendar'[Date]), MONTH)
)
var togetherValuePreviousMonth = 
CALCULATE(
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date]) //&& DATEDIFF('calendar'[Date], MAX('calendar'[Date]), MONTH)
),
PARALLELPERIOD('calendar'[Date],-1,MONTH)
)

var stopValue = 
CALCULATE(
CALCULATE(
    CALCULATE(
   [valuemeasure],
    PARALLELPERIOD('calendar'[Date], -1, MONTH))
    +
    TOTALMTD([valuemeasure], 'calendar'[Date]),
    'calendar'[Date] < MAX('calendar'[Date])),
 DATESMTD('calendar'[Date])
)

var cumulative = 
CALCULATE(
    IF( togetherValuePreviousMonth >= 0, currentValue, togetherValue),
    DATESINPERIOD('calendar'[Date], MAX('Table'[date]), -1, MONTH)
)

return cumulative

[cqueiroz2222]

Hello @vojtechsima 

Thank you for your patience and help. I believe it will not be possible to replicate the excel rule, as it continues to make mistakes at a certain point

[AnkitKukreja]

Hi! @cqueiroz2222 

Can you please explain your question again and best way would be to share your requirement and what is the outcome you are expecting.