Exclude last month in calculation

[Anonymous]

Hi,

I need to create a measure that calculates total sales excluding last month. So I used this formula:

CALCULATE(
    SUM(Sales[sale_sum]); 
    FILTER(
        Sales; 
        Sales[date] < DATE(YEAR(MAX(Sales[date])); MONTH(MAX(Sales[date])); 1)
    )
)

It actually works but when I try to use the measure in chart I don't see anything if I drill down to months.

Any help appreciated. Thanks.

[Stachu · Accepted solution]

your measure calculates last month for a given filter context, so if you go to month granularity it only considers that single month (and axcludes it)
this measure will look at the overall last month

VAR ExcludedLastDate = CALCULATE(MAX(Sales[date]), ALL(Sales))
RETURN
CALCULATE(
    SUM(Sales[sale_sum]); 
    FILTER(
        Sales; 
        Sales[date] < DATE(YEAR(ExcludedLastDate); MONTH(ExcludedLastDate)); 1)
    )
)