topic Re: Select latest dates in every month across many years data set in DAX Commands and Tips

Select latest dates in every month across many years data set

PubliusEnigma — Wed, 20 Nov 2019 15:43:30 GMT

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

Re: Select latest dates in every month across many years data set

Anonymous — Thu, 31 Oct 2019 11:33:22 GMT

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

PubliusEnigma

Re: Select latest dates in every month across many years data set

Anonymous — Thu, 31 Oct 2019 13:03:28 GMT

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())

Re: Select latest dates in every month across many years data set

PubliusEnigma — Wed, 20 Nov 2019 12:51:31 GMT

Anonymous Anonymous

Thank you very much - both solutions work very well!

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

Re: Select latest dates in every month across many years data set

PubliusEnigma — Wed, 20 Nov 2019 13:02:16 GMT

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

Re: Select latest dates in every month across many years data set

Anonymous — Wed, 20 Nov 2019 14:33:16 GMT

PubliusEnigma

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

Re: Select latest dates in every month across many years data set

PubliusEnigma — Wed, 20 Nov 2019 14:41:11 GMT

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