cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

1 ACCEPTED SOLUTION
Super User

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

5 REPLIES 5
Super User

Please show the definition of LASTMONTH

Frequent Visitor

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

Correct. Change that measure by removing the category filter.

Frequent Visitor

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?
Super User

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