Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Exclude last month in calculation

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.

Need Help
  • Stachu's avatar
    Stachu
    7 years ago

    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)
    )
)

1 Reply

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    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)
    )
)