Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Power BI doing wrong calculations

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])
)
Reports
  • DataNinja777's avatar
    DataNinja777
    1 year ago

    Anonymous ,

    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,

7 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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
  • DataNinja777's avatar
    DataNinja777
    Super User
    1 year ago

    Hi Anonymous ,

    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,

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        DataNinja777
        Super User
        1 year ago

        Anonymous ,

        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,