Forum Discussion
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...
ALLEXCEPT is a rather blunt instrument. Try using the more gentle REMOVEFILTERS.
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?
ALLEXCEPT is a rather blunt instrument. Try using the more gentle REMOVEFILTERS.