Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
julesdude
Post Partisan
Post Partisan

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

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.

4 REPLIES 4
amitchandak
Super User
Super User

@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...

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

@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  
aaa11a1aTermination Option31-Jul-226 3
bbb22a2aTermination Option01-Apr-23630-Sep-231
ccc33a3aTermination Option17-Jan-23617-Jul-231
ddd48026Termination Option31-Aug-221231-Aug-281
eee5a4a4Renew Extend07-Mar-22608-Sep-221
eee5a5a5Renew Extend07-Mar-22608-Sep-221
eee5a6a6Renew Extend07-Mar-22608-Sep-221
fff67777Termination Option31-Aug-23631-Aug-231
ggg78888Termination Option24-Jun-23630-Jun-231
hhh89999Renew Extend30-Nov-231301-Jan-251
hhh91010Renew Extend31-Mar-221401-Jun-231
hhh10222Renew Extend31-Mar-221401-Jun-231
hhh111111Renew Extend30-Jun-231201-Jul-241
hhh122a2aRenew Extend28-Feb-22901-Dec-221
iii131aRenew Extend01-Apr-221303-May-231
iii131aRenew Extend01-Apr-221303-May-231
hhh142a2aRenew Extend28-Feb-22901-Dec-221
hhh15222Renew Extend31-Mar-221401-Jun-231
hhh164444Renew Extend31-Mar-221401-Jun-231
hhh175555Renew Extend30-Apr-22901-Feb-231
jjj186666Renew Extend31-May-231201-Jun-241
jjj297777Renew Extend30-May-221201-Jun-231
kkk208888Renew Extend31-May-221301-Jul-231

 

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.

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)

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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.