Create End Date using Start Dates

LadyPeshet — Mon, 02 Jan 2023 09:26:01 GMT

Hello,

I have a table in Power BI like this:

ID START_DATE 1 06/24/2016 1 06/24/2017 1 06/24/2018 2 08/08/2017 2 08/08/2016 3 12/12/2015

I would like to create a calculated column using DAX, in which I have to calculate the End Date for each record and leave blank if it is MAX Start Date. Output should look as in the example below:

ID START_DATE END_DATE 1 06/24/2016 06/23/2017 1 06/24/2017 06/23/2018 1 06/24/2018 2 08/08/2017 2 08/08/2016 08/07/2017 3 12/12/2015

Currently I'm stuck with this solution where I can have MAX End Date for all of the historical records but it's not what I need:

End Date = VAR TableKey = 'Table1'[ItemKey] VAR CountDuplicates = CALCULATE ( COUNTROWS ( 'Table1' ), ALL ( 'Table1' ), 'Table1'[ItemKey] = TableKey ) VAR GetEndDate = CALCULATE ( MAX ( 'Table1'[Start_Date] ), FILTER ( 'Table1', 'Table1'[ItemKey] = EARLIER ( 'Table1'[ItemKey] ) ) ) RETURN IF ( AND ( CountDuplicates > 1, GetEndDate <> 'Table1'[Start_Date] ), GetEndDate - 1, BLANK () )

Could you please support how to get desired result?

Thank you in advance!

Re: Create End Date using Start Dates

amitchandak — Mon, 02 Jan 2023 10:17:23 GMT

LadyPeshet , Create a new column like

end date =

Var _min = Minx(filter( table, [ID] = earlier([ID]) && [Start Date] > earlier([Start Date]) ), [Start Date])

return

if(isblank(_min), blank(), _min-1)

Re: Create End Date using Start Dates

tamerj1 — Mon, 02 Jan 2023 10:44:18 GMT

Hi LadyPeshet
Please try

END_DATE = MINX ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ), 'Table'[START_DATE] > EARLIER ( 'Table'[START_DATE] ) ), 'Table'[START_DATE] ) - 1

topic Create End Date using Start Dates in DAX Commands and Tips

Create End Date using Start Dates

Re: Create End Date using Start Dates

Re: Create End Date using Start Dates