Forum Discussion

Annemie19's avatar
Annemie19
Helper II
6 years ago
Solved

Select first date in table after certain date

Hi there, 

 

I have a date specified by the user (AA Last Value Date), I need to write a measure to select the first projection date (ProjEndDate_Date) in another table after the value date. For instance, if my value date is 31/08/2020, and my Projection date column is as follows:

 

 

it needs to return 30 Sept 2020.

  • AntrikshSharma's avatar
    AntrikshSharma
    6 years ago

    Annemie19  Try this:

    AA Last Proj_date =
VAR LastValueDate =
    SELECTEDVALUE ( CashProjection[AA Last value date] )
VAR DatesGreaterThanValueDate =
    FILTER (
        SUMMARIZE (
            CashProjection,
            CashProjection[ProjEndDate_Date],
            CashProjection[AA Last value date]
        ),
        CashProjection[ProjEndDate_Date] > LastValueDate
    )
VAR NextImmediateDate =
    MINX ( DatesGreaterThanValueDate, CashProjection[ProjEndDate_Date] )
RETURN
    NextImmediateDate

4 Replies

  • AntrikshSharma's avatar
    AntrikshSharma
    Community Champion
    6 years ago

    Annemie19 I am just guessing this would work fo you based on the limited information. You will have to modify the table names accordingly.

     

    =
VAR LastValueDate =
    DATE ( 2020, 08, 31 ) -- If you want to remove the static date then use SELECTEDVALUES ( Table[Column] ) 
VAR DatesGreaterThanValueDate =
    FILTER (
        ALL ( Table ),
        Table[ProjectEndDates] > LastValueDate
    )
VAR NextImmediateDate =
    MINX ( DatesGreaterThanValueDate, Table[ProjectEndDates] )
RETURN
    NextImmediateDate

     

    • Annemie19's avatar
      Annemie19
      Helper II
      6 years ago

      Hi Antriksh, 

       

      Thanks a lot for your help. 

       

      For the screenshot below it is giving the correct date (AA Last Proj_date) which is 30 Sept 2020.

       

       

      But, for the next screenshot, it is giving 30 Sept as well. Even though it should be 20 Nov 2020. 

       

       

      The DAX is used is as follows:

      AA Last Proj_date =
      VAR LastValueDate = [AA Last value date]
      VAR DatesGreaterThanValueDate =
      FILTER (
      ALL (CashProjection), CashProjection[ProjEndDate_Date] > LastValueDate)
      VAR NextImmediateDate =
      MINX (DatesGreaterThanValueDate,CashProjection[ProjEndDate_Date])
      RETURN NextImmediateDate
       
      Can you maybe help me with why it is not giving the correct dates for different DealID's?
       
      Kind Regards, 
      Annemie
      • AntrikshSharma's avatar
        AntrikshSharma
        Community Champion
        6 years ago

        Annemie19  Try this:

        AA Last Proj_date =
VAR LastValueDate =
    SELECTEDVALUE ( CashProjection[AA Last value date] )
VAR DatesGreaterThanValueDate =
    FILTER (
        SUMMARIZE (
            CashProjection,
            CashProjection[ProjEndDate_Date],
            CashProjection[AA Last value date]
        ),
        CashProjection[ProjEndDate_Date] > LastValueDate
    )
VAR NextImmediateDate =
    MINX ( DatesGreaterThanValueDate, CashProjection[ProjEndDate_Date] )
RETURN
    NextImmediateDate
  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Annemie19 , In table 2, you can have a new column

     

    minx(filter(table1, table1[user] =table2[user]), table1[projenddate_date])

    minx(filter(table1, table1[user] ="AA"), table1[projenddate_date])

    minx(table1, table1[projenddate_date])