cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

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:

 

Annemie19_0-1598621065504.png

 

it needs to return 30 Sept 2020.

1 ACCEPTED SOLUTION

@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

Thank you,
Antriksh Sharma

View solution in original post

4 REPLIES 4
Super User IV
Super User IV

@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])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Super User II
Super User II

@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

 


Thank you,
Antriksh Sharma

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.

 

112.JPG

 

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

 

111.JPG

 

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

@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

Thank you,
Antriksh Sharma

View solution in original post

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.