Obtaining a Date by Filtering a Minimum Date as well as Two Other Conditions for EDATE

[julesdude]

Hi,

I have the following code which doesn't work:

           EDATE (
                CALCULATE (
                    MIN ( Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                ),
                CALCULATE (
                    SUM ( Option[Notice Period] ),
                   MINX ( Option, Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                )
            )

I am trying to find the lowest date found in the [Option Start Date] column after filtering out rows that don't have an [Option Category] as 'Termination Option' and where [Option Start Date] is greater than variable _asofdate - which is assigned to a date picker in my report.
My trouble begins with the next statement where the EDATE requires a number to advance the date in months.
It throws an error because I am not declaring a true/false expression with MINX.
What I want it to do is essentially use the same row that was filtered in the first statement that got the start date, but to return whatever is in the [Notice Period] column of that row. 

I was trying to place the MIN/MINX so that the results returned would mirror the filtering applied from the first statement.

What is the correct and most efficient way of achieving this?

Thank you.

[amitchandak]

@julesdude ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

You can refer this approach, you need Min instead of MAX

Latest
https://amitchandak.medium.com/power-bi-get-the-last-latest-value-of-a-category-d0cf2fcf92d0

https://amitchandak.medium.com/power-bi-get-the-sum-of-the-last-latest-value-of-a-category-f1c839ee8...

[julesdude]

@amitchandak 

Here's a sample dataset to help.

The table is called Option and only this table is referenced for the DAX. 

Asset Reference	Lease Reference	Unit Reference	Option Category	Option Start Date	Notice Period	Option Effective Date	Score
aaa	1	1a1a	Termination Option	31-Jul-22	6		3
bbb	2	2a2a	Termination Option	01-Apr-23	6	30-Sep-23	1
ccc	3	3a3a	Termination Option	17-Jan-23	6	17-Jul-23	1
ddd	4	8026	Termination Option	31-Aug-22	12	31-Aug-28	1
eee	5	a4a4	Renew Extend	07-Mar-22	6	08-Sep-22	1
eee	5	a5a5	Renew Extend	07-Mar-22	6	08-Sep-22	1
eee	5	a6a6	Renew Extend	07-Mar-22	6	08-Sep-22	1
fff	6	7777	Termination Option	31-Aug-23	6	31-Aug-23	1
ggg	7	8888	Termination Option	24-Jun-23	6	30-Jun-23	1
hhh	8	9999	Renew Extend	30-Nov-23	13	01-Jan-25	1
hhh	9	1010	Renew Extend	31-Mar-22	14	01-Jun-23	1
hhh	10	222	Renew Extend	31-Mar-22	14	01-Jun-23	1
hhh	11	1111	Renew Extend	30-Jun-23	12	01-Jul-24	1
hhh	12	2a2a	Renew Extend	28-Feb-22	9	01-Dec-22	1
iii	13	1a	Renew Extend	01-Apr-22	13	03-May-23	1
iii	13	1a	Renew Extend	01-Apr-22	13	03-May-23	1
hhh	14	2a2a	Renew Extend	28-Feb-22	9	01-Dec-22	1
hhh	15	222	Renew Extend	31-Mar-22	14	01-Jun-23	1
hhh	16	4444	Renew Extend	31-Mar-22	14	01-Jun-23	1
hhh	17	5555	Renew Extend	30-Apr-22	9	01-Feb-23	1
jjj	18	6666	Renew Extend	31-May-23	12	01-Jun-24	1
jjj	29	7777	Renew Extend	30-May-22	12	01-Jun-23	1
kkk	20	8888	Renew Extend	31-May-22	13	01-Jul-23	1

My whole measure, that doesn't work:

Next Option Effective Date = 
VAR _asofdate = [As Of Date]
VAR _score =
    SUM ( Option[Score] )
VAR _date =
    SWITCH (
        TRUE (),
        _score = 3,
            EDATE (
                CALCULATE (
                    MIN ( Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                ),
                CALCULATE (
                    SUM ( Option[Notice Period] ),
                   MINX ( Option, Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                )
            ),
        _score = 4,
            EDATE (
                _asofdate,
                CALCULATE (
                    SUM ( Option[Notice Period] ),
                  MINX ( Option, Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                )
            ),
        BLANK ()
    )
RETURN
    _date

I want to achieve the following:

1. for score = 3 - to get the second argument of EDATE to advance the date in months. It needs to get this from the [Notice Period] column by filtering only rows where [Option Start Date] is > _asofdate AND where [Option Category] = 'Termination Option'. Then get the [Notice Period] from the row where the [Option Start Date] is closest to  _asofdate

2. for score = 4 - to get the second argument of EDATE to advance the date in months I need to do exactly the same as above.

Thank you.

[FreemanZ]

hi @julesdude 

you have provided a nice dataset. what do you expect out of this? (try to explain in plain language without referring to what you tried so far)

[julesdude]

Hi @FreemanZ 

Yes I managed to figure out a way from the links @amitchandak sent, in which the solutions I could adapt a little for this problem I was having.

I dropped out the two lines that were causing the issue:

MINX ( Option, Option[Option Start Date] )

Of course, you cannot use such a statement as a true or false expression.

I instead replaced it with:

        _score = 3,
            EDATE (
                CALCULATE (
                    MIN ( Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                ),
                CALCULATE (
                    SUM ( Option[Notice Period] ),
                    Option[Option Start Date] = MIN ( Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                )
            ),
        _score = 4,
            EDATE (
                _asofdate,
                CALCULATE (
                    SUM ( Option[Notice Period] ),
                    Option[Option Start Date] = MIN ( Option[Option Start Date] ),
                    Option[Option Category] = "Termination Option",
                    Option[Option Start Date] > _asofdate
                )
            ),

Still need to test it more but that seems to provide me with the filtered row option i need.