Forum Discussion

ashas's avatar
ashas
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 month spend" .. where the current month would be the month I have the most recent transaction for.

So for FY23,  LASTMONTH = 5 (May)

For FY22 ,  LASTMONTH = 6 (June)

 

 

My "Current Month SPend" calculation is:

Current month spend =
VAR FY = SELECTEDVALUE(Dates[FY])
RETURN
CALCULATE([ABS Amount], FILTER(Dates, Dates[MonthOfYear]=[LASTMONTH] && Dates[FY]=FY))

 

Which gives the following result (and is wrong!) notice the "Kids activities" category is showing a value which is incorrect

 

If I hard code the month value into the Current Month Spend then it works perfectly:

Current month spend =

VAR FY = SELECTEDVALUE(Dates[FY])

RETURN
CALCULATE([ABS Amount], FILTER(Dates, Dates[MonthOfYear]=5 && Dates[FY]=FY))


 

See the categories that don't have transactions in May are showing as blank..

 

What am I doing wrong?  

 

Thanks for any feedback,

Asha

 

 

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago

    ALLEXCEPT is a rather blunt instrument. Try using the more gentle REMOVEFILTERS.

5 Replies

    • ashas's avatar
      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's avatar
        lbendlin
        Super User
        3 years ago

        Correct. Change that measure by removing the category filter.