Date prior to MAX date

[StephanK]

Hello All,

I have created a dashboard for Accounts Receivable, the data is manually taken from the source system and stored in an excel file containing the date in the name. The date in the name of the file is then used in the dashboard to show the latest balances by using the following formula:

sum({<[Report Date.autoCalendar.Date]={"$(=date(max([Report Date.autoCalendar.Date]),'DD/MM/YYYY'))"}>}[Balance EUR])

This gives me the latest balance coming from the excel file with the latest date. However i would like to add a feature to the  dashboard that it shows also the change versus the prior download. This way we can see easily which invoices were paid or which new invoices have been issued. 

So basically it would still be the above mentioned formula, but instead of finding the MAX date, i need to find the date prior to MAX date. How can i do that? I cannot  work with a fixed interval of days, because we run the report manually and it is not  always exactly 1 week in between.

Thanks in advance!

[tresesco · Accepted solution]

You are essentially looking foe second-max date. Try using rank parameter in max(), like:

max([Report Date.autoCalendar.Date] , 2)

[StephanK]

Hi Tresesco,

Wow that was quick and easier  than expected 🙂

Thanks a lot!

Kind regards,

Stephan