Forum Discussion
Solved
Create End Date using Start Dates
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!
Hi LadyPeshet
Please tryEND_DATE = MINX ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ), 'Table'[START_DATE] > EARLIER ( 'Table'[START_DATE] ) ), 'Table'[START_DATE] ) - 1
2 Replies
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)
Hi LadyPeshet
Please tryEND_DATE = MINX ( FILTER ( CALCULATETABLE ( 'Table', ALLEXCEPT ( 'Table', 'Table'[ID] ) ), 'Table'[START_DATE] > EARLIER ( 'Table'[START_DATE] ) ), 'Table'[START_DATE] ) - 1
