
1. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?
swaroop.gantela Jul 9, 2019 6:03 PM (in response to Jeffrey Ten Grotenhuis)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?
Chris McClellan Jul 9, 2019 6:32 PM (in response to Jeffrey Ten Grotenhuis)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?
Jeffrey Ten Grotenhuis Jul 10, 2019 5:26 PM (in response to swaroop.gantela)[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?
Jeffrey Ten Grotenhuis Jul 10, 2019 5:31 PM (in response to Jeffrey Ten Grotenhuis)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?
swaroop.gantela Jul 10, 2019 5:39 PM (in response to Jeffrey Ten Grotenhuis)1 of 1 people found this helpfulJeffrey,
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:
How to Combine Multiple Date dimensions to Refer to in a Count?

6. Re: How do I calculate the MIN of a set of dates that at the same time meets my filter criteria?
Jeffrey Ten Grotenhuis Jul 10, 2019 6:39 PM (in response to swaroop.gantela)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