Grand Totals are not summing correctly for previous month

[rachaelwalker]

I am trying to create a measure or calculated column to sum the value for each previous month and to reflect the accurate total but my total is showing January's value and not the sum.

This is my measure. How can I get this to sum it correctly and filter just the previous month

50% Next Previous Month = 
CALCULATE (
    SUM ( 'Opportunities'[50% Next] ),
    PREVIOUSMONTH ( 'Calendar'[Date] )
)

[v-yanimei-msft]

Hi @rachaelwalker , 

Thank you for reaching out with your issue regarding the calculation of totals for the previous month in Power BI. Based on the measure you've shared, it seems like you're on the right track with using and functions. Depending on your description, perhaps adjusting the measure will achieve the effect you want. Here's a slightly modified version of your measure that ensures the context is set correctly:

Next Previous Month = 
CALCULATE (
    SUM ( 'Opportunities'[50% Next] ),
    FILTER (
        ALL ( 'Calendar'[Date] ),
        'Calendar'[Date] > EOMONTH ( TODAY(), -2 ) && 'Calendar'[Date] <= EOMONTH ( TODAY(), -1 )
    )
)

You can adjust the date logic as needed to fit your scenario.

If you have any challenges or other issues implementing this solution, can you share the specific data ( including table and model ) you have used in text and image format? In addition to this, you also need to determine if there are any specific filters or slicers applied to the report, as this may affect this calculation. This will help diagnose the problem more effectively.

Best Regards,

Caroline Mei

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

[rachaelwalker · Accepted solution]

Thank you for taking the time to help! With this being a forecast, I need it to return the previous month based on the selected date/fitler. I fixed it using the DAX below. I am still learning why to use certain functions and what the impact is. I have saved your measure for future use. Thank you!

50% Next Previous Month = 
CALCULATE (
    SUM ( 'Opportunities'[50% Next] ),
    DATEADD('Calendar'[Date],-1,MONTH)
)