Skip to main content
cancel
Showing results for 
Search instead 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

Reply
ashas
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

ashas_0-1683805572010.png

 

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


ashas_1-1683805967247.png

 

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

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

View solution in original post

5 REPLIES 5
lbendlin
Super User
Super User

Please show the definition of LASTMONTH

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)

Correct. Change that measure by removing the category filter.

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.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors