Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
ashas
Frequent Visitor

Filter 2 different tables to calculate amount

Hi Folks,

 

I'm trying to do something that *I think* should be possible but it's not working quite as expected..

 

I have a Dates table linked to a Transactions table on "Date"

 

dimDates             factTransactions

- Date ..............     - transactionDate

- FY                     - transactionAmount

                           - transactionCategory

                    

I have a selector on the dashboard that allows me to filter the dates table by financial year. So effectively when I click the FY button then my transactions tables should show me all transactions for the financial year selected.. 

 

What I want to do is the following:

- filter the transactions table based on financial year selected

- filter the transactions table to show all transactions for the most recent month of data within the transactions table for the selected FY only

     example:

     If the last transaction date was in May, then I would want to see ALL transactions for MAY for FY23  

 

- Once I have this sub table, I want to SUM the transaction Amount, for MAY only

 

I've tried the following but I can't get the second part to work (where I need to filter the transactions table on lastdate month)

 

SelectedFY = SELECTEDVALUE(dimDates[FY])

CALCULATE(SUM(factTransactions.transactionAmount)),FILTER(dimDates,dimDates[FY]=Selected FY),FILTER(factTransactions, MONTH(facttransactions[transactionDate]) = MONTH(LASTDATE(factTransactions[transactionDate))))

 

Hope someone can help with this!

 

1 REPLY 1
AjithPrasath
Resolver II
Resolver II

Hi ashas,

  Please try the below code:

 

SelectedFY = SELECTEDVALUE(dimDates[FY])

CALCULATE(
SUM(factTransactions[transactionAmount]),
FILTER(
dimDates,
dimDates[FY] = SelectedFY && dimDates[Date] = EOMONTH(LASTDATE(dimDates[Date]), 0)
)
)

 

PS: If it is working, please mark it as the solution

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

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