Power BI doing wrong calculations

[cqueiroz2222]

Hello, I'm making a table that calculates the previous month and adds it to the current month's result.

Power bi makes the first line correctly, but then the calculations are wrong as you can see in the image below: 

The error can be seen in abr/2023, if we calculate manually -29,813 + (-67,166) = -96,979 and not -96.229 as shown in the table. Help me see what could be wrong, please

DAX Measures Below:

C/C =

VAR vResult = [Result]

VAR vPreviousMonth = [Previous Month]

RETURN

    IF(vPreviousMonth < 0,

        CALCULATE(vResult + vPreviousMonth, vResult

    )

--------------------------------------------------------------------------------

Previous Month =

CALCULATE(

    [Result],

    PREVIOUSMONTH('dim_Calendario'[Data])

)

[DataNinja777]

Hi @cqueiroz2222 ,

The DAX for your C/C measure has a couple of issues. It appears that there is a syntax error in how you’re adding vResult and vPreviousMonth. Additionally, the IF statement might not be necessary if we intend to always add the previous month's result to the current one, regardless of its sign.

C/C = 
VAR vResult = [Result]
VAR vPreviousMonth = [Previous Month]

RETURN
    IF(
        NOT ISBLANK(vPreviousMonth),
        vResult + vPreviousMonth,
        vResult
    )

Best regards,

[cqueiroz2222]

Hello @DataNinja777 

The measure you sent did "work" to me, however PBI still makes the wrong calculation, as you can see in the image below:

The first to lines the calculation is made correctly, but the error apperas when he should calculate:

(-29.813) + (-67.166) = -96.979 and not -96.229

Do you have any other idead in how could I calculate this?

[DataNinja777 · Accepted solution]

@cqueiroz2222 ,

To ensure the cumulative total is calculated correctly for each month without relying on intermediate or potentially blank values, we can use a SUMX approach. This modified measure iterates over all dates up to and including the current month and adds up the Result values.

Here's the modified measure to accumulate properly:

Cumulative Total = 
VAR CurrentDate = MAX('Calendar'[Date])
RETURN
    SUMX(
        FILTER(
            ALL('Calendar'),
            'Calendar'[Date] <= CurrentDate
        ),
        [Result]
    )

• CurrentDate: This variable captures the current row's date in the context, which acts as the endpoint for our cumulative calculation.
• FILTER(ALL('Calendar'), 'Calendar'[Date] <= CurrentDate): This filter retrieves all rows from the Calendar table up to the current date, removing any filters that might interfere.
• SUMX(..., [Result]): By iterating over each date in the filtered range, this formula sums up all [Result] values up to the CurrentDate, ensuring an accurate cumulative calculation.

Best regards,

[cqueiroz2222]

Now it worked, thank you for your help!!! 😄

[cqueiroz2222]

Hello, @DataNinja777 , me again! 😃

Can you please help me to add one more condition (if it would be possible)

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]

[cqueiroz2222]

Hello @Anonymous , thank you for your help.

However, my dax measuares are in a separate table called 'Measures', does it change anythig? Because I tried to use and it didn't work.

C/C =

var _t = ADDCOLUMNS('Medidas',"Total",

    SUMX(FILTER(ALL('dim_Calendario'),[Month]<=EARLIER([Month])),[Result]))

RETURN

IF(MAX('Medidas'[Result])<0,MAXX(_t,[Total]),MAX('Medidas'[Result]))

Also this "EARLIER([Month]" didnt work

[Anonymous]

Hi @cqueiroz2222 ,

I made simple samples and you can check the results below:

Measure = var _t = ADDCOLUMNS('Table',"Total",SUMX(FILTER(ALL('Table'),[ID]<=EARLIER([ID])),[Result]))
RETURN IF(MAX('Table'[Result])<0,MAXX(_t,[Total]),MAX('Table'[Result]))

Replacing the ID in the sample with your date should accomplish your goal.

An attachment for your reference. Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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