Forum Discussion

Frequent Visitor

3 years ago

Solved

Calculating sum for current month only

Hi Folks, Hope you can help.. I've tried a few different things and can't get this one to work.. So I have a transactions table linked to a dates table... I'm trying to work out the "Current mo...

lbendlin
3 years ago
ALLEXCEPT is a rather blunt instrument. Try using the more gentle REMOVEFILTERS.

Super User

3 years ago

Please show the definition of LASTMONTH

ashas
Frequent Visitor
3 years ago
Hello, thanks for your reply... LASTMONTH is..

LASTMONTH = MONTH(LASTDATE(factTransactions[Date]))

I think it's getting the LASTMONTH as 4 for "Kids activities" because that is the last transaction date for that category (so that makes sense but not what I want for this)
- lbendlin
  Super User
  3 years ago
  Correct. Change that measure by removing the category filter.
  - ashas
    Frequent Visitor
    3 years ago
    Nearly have it!
    
    So I changed LASTMONTH to be:
    LASTMONTH = CALCULATE(MONTH(LASTDATE(factTransactions[Date])),FILTER(ALLEXCEPT(factTransactions,factTransactions[Date]),MAXA(factTransactions[Date])))
    
    And Current month spend is:
    Current month spend = CALCULATE(SUM(factTransactions[Amount_abs]),filter(dimDates,dimDates[FY]=[Sel_FY] && dimDates[MonthOfYear]=[LASTMONTH]))
    
    This seems to work, except I need LASTMONTH to return the transaction date month for the selected financial year...
    
    SO I thought putting in a check for FY in LASTMONTH like the below would work:
    
    LASTMONTH = CALCULATE(MONTH(LASTDATE(factTransactions[Date])),FILTER(dimDates,dimDates[FY]=[Sel_FY]),FILTER(ALLEXCEPT(factTransactions,factTransactions[Date]),MAXA(factTransactions[Date])))
    
    But adding the date filter into LASTMONTH measure seems to break it again and I get incorrect values in my "Current month spend" measure..
    
    Any hints?