Find the Forex Rate for the last day of the month for any transactions within that month
I am trying to create a measure that will find and use the Forex Rate from the last day of the month for any transaction within that month. I have the below measure which works for finding the Forex Rate that matches the transaction date for transactions that are related to Revenue and Cost of Sales (and accounts for gaps in Forex Rates over weekends). However, for internal General Ledger Journals, we need to use a general reporting Forex Rate, which we input into our system, with an end of month date (29, 30, or 31, etc.). How do I amend the below measure to find the Forex Report Rate at the end of each month for those General Ledger Journals with transaction dates within that month? Fx to USD Report = IF( SELECTEDVALUE( GLTrans[BaseCurr] ) = "USD", 1, MAXX( TOPN( 1, FILTER( ALL( ForexRates ), ForexRates[Base Currency] = SELECTEDVALUE( GLTrans[BaseCurr] ) && ForexRates[Forex Date] <= MAX( GLTrans[DateTrans] ) ), ForexRates[Forex Date], DESC ), ForexRates[Report Rate] ) )