6 Replies Latest reply on Jul 10, 2019 6:39 PM by Jeffrey Ten Grotenhuis

# How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

Goal: to get the date of the first day of the current fiscal period.

By combining the fields below, I can filter my data to the current fiscal period:

• [FQ (Closed Date)] has the value "Current," which allows me to filter to the current fiscal quarter (of the current year).
• [Current FP Number] returns the number (1-12) of the current fiscal period (as of writing this on July 9th, I am in period 8, as seen below) I've tried adding the filters above to the Context and then making this formula

MIN(

MIN(

DATETRUNC('week',[Close Date],'saturday')

,DATETRUNC('week',[Close Date]-7,'saturday')

)

,DATETRUNC('week',[Close Date]-14,'saturday')

)

I designed it this way because nothing was closed last week, so if I don't add the row with -7, then the formula returns 9/6/2019. And I added the row with -14 just in case nothing is closed for two weeks.

The problem is that the formula currently returns 6/22/2019, which is in period 7.

Is there a way to return the minimum value that matches my filter criteria? In this case, the correct answer would be 6/29/2019.

I'm sorry I can't share the workbook, it's confidential.

• ###### 1. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

Jeffrey,

I think it will depend on how your data is set up.

Is [Current FP Number] coming from your datasource, or is that calculated?

If it can be used as a dimension, would it be possible to use a level of detail calculation like:

{ FIXED [Current FP Number] : MIN ( [Close Date]) }

Another method I've seen is to get the last date of the previous period and then add one to it.

If possible, would be grateful if you could post some fake dates that are in the structure of your true source.

• ###### 2. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

Jeffrey Ten Grotenhuis wrote:

I'm sorry I can't share the workbook, it's confidential.

We understand that the data is confidential, maybe the structure of the workbook is confidential, but a single sheet (copy the sheet into a different workbook) with randomised data would really help.

That said, I would try a WINDOW_MIN() table calculation.  If you use an LOD you will probably have to change the filters to context but an ordinary table calc might work better.

• ###### 3. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

[Current FP Number] comes from the datasource

So I can get the earliest Close Date of the Fiscal Period, but the problem is that I need the earliest date of the Fiscal Period, and I'm just suing Close Date as a means to get there. Sometimes there's no deal closed on the first day of the Fiscal Period or even on the first week, so I'm trying to find a foolproof way to get to the first day of the Fiscal Period without my formula taking me to the previous Fiscal Period.

I think I'll run into the same problem while trying to get to the last day of the previous Fiscal Period (I might overshoot the formula and end up in the current fiscal period), but that's a good suggestion.

This probably helps: I had my data filtered, so there were some weeks without a close date, but if I remove all the filters, there should be a close date in every week, so I can do DATETRUNC('week',MIN([Close Date]),'saturday') the only obstacle now is that for some reason when I try to filter the view to this fiscal period using

IF [Close Date] = TODAY() THEN [FP (Closed Date)] END the only close date available in the view is today, even though the result of the calculation is "08," and there are many Close Dates in Fiscal Period 08.

• ###### 4. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

I had my data filtered, so there were some weeks without a close date, but if I remove all the filters, there should be a close date in every week, so I can do DATETRUNC('week',MIN([Close Date]),'saturday') and that works. I can also use the formula below in case something wasn't closed during the first week, but something was created in that week, to cover my bases (or keep adding other opportunity dates until there's no chance that I won't get a hit on the first week of the period. Thanks for the help.

MIN(

DATETRUNC('week',MIN([Close Date]),'saturday')

,DATETRUNC('week',MIN([Created Date]),'saturday')

)

• ###### 5. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

Jeffrey,

I think I'm starting to see.

I'm not sure if this will work for your setup, but you could try using a date scaffold,

which is just a single column of every date, stretching as far into the future as you would like.

You would then left join your dates to this scaffold and then will be assured to always have the first

date of the period.

Here's some examples:

1 of 1 people found this helpful
• ###### 6. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?

I'm using a Live source, so I'm not sure whether that would work for me. But it's very interesting and useful, so I'm going to request that our data guy create a lice data source that is a data scaffold. That should make finding the first and last days of a range a lot easier. Thanks