cancel
Showing results 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

Frequent Visitor

## Select latest dates in every month across many years data set

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? 😉

1 ACCEPTED SOLUTION
Anonymous
Not applicable
``````Column =
VAR maxDate = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Date].[Month],Test[Date].[Year]))
RETURN  CALCULATE(SUM(Test[Rate]),Test[Date]=maxDate)``````
6 REPLIES 6
Frequent Visitor

@Anonymous @Anonymous

Thank you very much - both solutions work very well!

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

Frequent Visitor

@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
Not applicable
``````Column =
VAR maxDate = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Date].[Month],Test[Date].[Year]))
RETURN  CALCULATE(SUM(Test[Rate]),Test[Date]=maxDate)``````
Frequent Visitor

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

Anonymous
Not applicable

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
Not applicable
``````Column =
VAR maxDate = CALCULATE(MAX(Test[Date]),ALLEXCEPT(Test,Test[Date].[Month]))
RETURN  CALCULATE(SUM(Test[Rate]),Test[Date]=maxDate)``````

@PubliusEnigma

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.