Date prior to MAX date

StephanK — Mon, 08 Jun 2020 08:11:02 GMT

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!

Re: Date prior to MAX date

tresB — Mon, 08 Jun 2020 08:15:04 GMT

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

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

Re: Date prior to MAX date

StephanK — Mon, 08 Jun 2020 08:24:41 GMT

Hi Tresesco,

Wow that was quick and easier than expected 🙂

Thanks a lot!

Kind regards,

Stephan

topic Date prior to MAX date in App Development

Date prior to MAX date

Re: Date prior to MAX date

Re: Date prior to MAX date