Calculating sum for current month only

[ashas]

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

[lbendlin]

Please show the definition of LASTMONTH

[ashas]

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]

Correct. Change that measure by removing the category filter.

[ashas]

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? 

[lbendlin · Accepted solution]

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