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!
