Forum Discussion
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 in...
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
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
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