Select latest dates in every month across many years data set

[PubliusEnigma]

Hello Community!

I'm quite new in DAX and unfortunately came on a quite complicated case.

I have a table with exchange rates. I need to extract exchange rates for the last day of each month.

I cannot use the function that gives me the last day of the month, because sometimes the last day of the month is not the last day when the exchange rate was issued. Below is the initial table:

Bank	From	To	Date	Rate
ECB	EUR	USD	28.10.2019	23,21
ECB	EUR	USD	27.10.2019	13,21
ECB	EUR	USD	26.10.2019	22,21
ECB	EUR	USD	25.10.2019	43,21
ECB	EUR	USD	28.09.2019	53,21
ECB	EUR	USD	27.09.2019	23,21
ECB	EUR	USD	26.09.2019	13,21
ECB	EUR	USD	28.08.2019	22,21
ECB	EUR	USD	27.08.2019	43,21
ECB	EUR	USD	26.08.2019	53,21
ECB	EUR	USD	25.08.2019	73,21
ECB	EUR	USD	24.08.2019	93,21

After the transition I'd like to have:

Bank	From	To	Date	Rate
ECB	EUR	USD	28.10.2019	23,21
ECB	EUR	USD	28.09.2019	53,21
ECB	EUR	USD	28.08.2019	22,21

Any clues Dear Masters of DAX? 😉

[PubliusEnigma]

@Anonymous @Anonymous 

Thank you very much - both solutions work very well!

I've learned a lot here, so thank you again for this! 🙂

[PubliusEnigma]

@Anonymous @Anonymous 

Oh... I'm afraid something is not right with those formulas - both calculate the right values but just within one calendar year. I need to extract those values for the whole database that has years back data. For now, I can't get a value for e.g. 31.03.2014 - it is blank...

Any ideas on fixing this? 🙂

[Anonymous · Accepted solution]

@PubliusEnigma 

Column = 
VAR maxDate = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Date].[Month],Test[Date].[Year]))
RETURN  CALCULATE(SUM(Test[Rate]),Test[Date]=maxDate)

[PubliusEnigma]

I don't know exactly why it works, but it looks like it works! Thank you again!

[Anonymous]

Hi @PubliusEnigma 

I am dealing with a similar problem and I have fixed it using a column like this one:

last_Rate = IF(CALCULATE(MAX ('Table_Name'[Date]); ALLEXCEPT(Table_Name;Table_Name[month_date]))=Table_Name[Date];Table_Name[Rate];BLANK())

[Anonymous]

Column = 
VAR maxDate = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Date].[Month]))
RETURN  CALCULATE(SUM(Test[Rate]),Test[Date]=maxDate)

@PubliusEnigma