Create start date and end date from date

[eng_123]

Hi All please let me know if you we can create a start date and end date from a single column Date

Existing Data:

Phase	Date
Phase1	15/10/2024
Phase1	16/10/2024
Phase1	17/10/2024
Phase 2	16/10/2024
Phase 2	17/10/2024
Phase1	31/10/2024
Phase1	1/11/2024
Phase1	2/11/2024
Phase 2	22/10/2024
Phase 2	23/10/2024

Create a new Dax calculation for start date and end date

start date - Is the start of continuous date from Date column

end date - is the end of continuous date from Date column

Example:

Phase	Start Date	End Date
Phase 1	15/10/2024	17/10/2024
Phase 1	31/10/2024	2/11/2024
Phase 2	16/10/2024	17/10/2024
Phase 2	22/10/2024	23/10/2024

[tharunkumarRTK · Accepted solution]

@eng_123 

I think implementing this solution in power bi would be a much easier, please find the PBIX file attached.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this. Due to the table not containing ids this took a bit of trial and error:

EVALUATE

VAR _vtable1 =

    ADDCOLUMNS(

        'Table (44)',

        "PreviousDate",

        VAR _phase = [Phase]

        VAR _date = [Date]

        VAR _previous =

            CALCULATE(

                MAX('Table (44)'[Date]),

                FILTER(

                    'Table (44)',

                    [Phase] = _phase && 'Table (44)'[Date] < _date

                )

            )

        RETURN IF(_previous = _date - 1, _previous, BLANK())

    )

-- Compute the start date for each continuous block

VAR _startdate =

    ADDCOLUMNS(

        _vtable1,

        "StartDate",

        IF(

            ISBLANK([PreviousDate]),

            [Date],

            BLANK()

        )

    )

-- Identify the end date by checking for gaps in the next date

VAR _enddate =

    ADDCOLUMNS(

        _startdate,

        "EndDate",

        VAR _phase = [Phase]

        VAR _date = [Date]

        VAR _next =

            CALCULATE(

                MIN('Table (44)'[Date]),

                FILTER(

                    'Table (44)',

                    [Phase] = _phase && 'Table (44)'[Date] > _date

                )

            )

        RETURN IF(_next <> _date + 1 || ISBLANK(_next), _date, BLANK())

    )

-- Summarize the result to get start and end dates for each continuous range

VAR _result =

    VAR _vtable2 = GROUPBY(_enddate, [Phase], [StartDate], [EndDate])

    RETURN

        ADDCOLUMNS(

            _vtable2,

            "edate",

            VAR _phase = [Phase]

            VAR calculatedEdate =

            var _sdate = [StartDate] RETURN

                CALCULATE(

                    MINX(FILTER(_vtable2, [Phase] = _phase && [EndDate]>_sdate), [EndDate])

                )

            RETURN

                IF(

                    calculatedEdate < [EndDate],

                    [EndDate],  -- Return the original EndDate if calculatedEdate is smaller

                    calculatedEdate  -- Otherwise, return calculatedEdate

                )

        )

RETURN

GROUPBY(FILTER(_result,NOT(ISBLANK([StartDate]))),[Phase],[StartDate],[edate])




End result shown in query view:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[Jihwan_Kim · Accepted solution]

Hi, 

If your expected result is to have a new table, please check the below picture and the attached pbix file.

expected result table = 
	VAR _t = ADDCOLUMNS(
		Data,
		"@prevdate", MAXX(
			OFFSET(
				-1,
				Data,
				ORDERBY(
					Data[Date],
					ASC
				),
				,
				PARTITIONBY(Data[Phase]),
				MATCHBY(
					Data[Phase],
					Data[Date]
				)
			),
			Data[Date]
		)
	)
	VAR _condition = ADDCOLUMNS(
		_t,
		"@condition", IF(
			INT(Data[Date] - [@prevdate]) = 1,
			0,
			1
		)
	)
	VAR _group = ADDCOLUMNS(
		_condition,
		"@group", SUMX(
			WINDOW(
				1,
				ABS,
				0,
				REL,
				_condition,
				ORDERBY(
					Data[Date],
					ASC
				),
				,
				PARTITIONBY(Data[Phase]),
				MATCHBY(
					Data[Phase],
					Data[Date]
				)
			),
			[@condition]
		)
	)
	VAR _result = ADDCOLUMNS(
		SUMMARIZE(
			_group,
			Data[Phase],
			Data[Date],
			[@group]
		),
		"StartDate", MINX(
			FILTER(
				_group,
				Data[Phase] = EARLIER(Data[Phase]) && [@group] = EARLIER([@group])
			),
			Data[Date]
		),
		"EndDate", MAXX(
			FILTER(
				_group,
				Data[Phase] = EARLIER(Data[Phase]) && [@group] = EARLIER([@group])
			),
			Data[Date]
		)
	)

	RETURN
		SUMMARIZE(
			_result,
			Data[Phase],
			[StartDate],
			[EndDate]
		)

[ThxAlot · Accepted solution]

Whether in DAX or PQ, either is easy.

let
    Source = DATA,
    #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}}),
    #"Grouped per Phase" = Table.Group(#"Sorted Rows", "Phase", {"grp per Phase", each Table.Group(Table.AddIndexColumn(_, "Index"), {"Date","Index"}, {"grp per Date", each _}, 0, (x,y) => Byte.From(Duration.TotalDays(y[Date]-x[Date])<>y[Index]-x[Index]))}),
    #"Expanded grp per Phase" = Table.ExpandTableColumn(#"Grouped per Phase", "grp per Phase", {"grp per Date"}),
    #"Transformed Columns" = Table.TransformColumns(#"Expanded grp per Phase", {"grp per Date", each [Start = [Date]{0}, End = List.Last([Date])]}),
    #"Expanded grp per Date" = Table.ExpandRecordColumn(#"Transformed Columns", "grp per Date", {"Start", "End"})
in
    #"Expanded grp per Date"

[DataNinja777]

Hi @eng_123 ,

You've already been provided with several solutions, and here’s another one. Since the DAX engine doesn't inherently preserve the original data order, I used Power Query (M) to lock in the data’s original sequence.

let
    // Reference the user's existing query/table (Replace "YourTableName" with the actual table/query name)
    Source = YourTableName,

       // Change the types of columns
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Phase", type text}, {"Date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each ([Phase] <> "Phase")),

    // Add a Group Number based on Phase changes using List.Accumulate
    GroupedWithPhaseChange = List.Accumulate(
        Table.ToRecords(#"Filtered Rows"), 
        { [GroupNumber = 1, PreviousPhase = null, Rows = {}] }, 
        (state, current) => 
            let 
                currentGroup = if state{0}[PreviousPhase] <> null and current[Phase] <> state{0}[PreviousPhase] 
                    then state{0}[GroupNumber] + 1 
                    else state{0}[GroupNumber],
                newRow = Record.AddField(current, "PhaseGroupIndex", currentGroup),
                updatedState = { 
                    [GroupNumber = currentGroup, PreviousPhase = current[Phase], Rows = List.Combine({state{0}[Rows], {newRow}})]
                }
            in 
                updatedState
    ){0}[Rows],

    // Convert the list of records back to a table, respecting the original order
    #"Grouped Table" = Table.FromRecords(GroupedWithPhaseChange)
    
in
    #"Grouped Table"

The formula above will preserve the order in the original data, as shown below:

You can then summarize the OriginalTable by using the following DAX table formula:

Table = SUMMARIZE('OriginalTable',OriginalTable[Phase],OriginalTable[Start Date],OriginalTable[End Date])

This will generate the output shown below:

I have attached an example pbix file.

Best regards,

[Jihwan_Kim · Accepted solution]

Hi, 

If your expected result is to have a new table, please check the below picture and the attached pbix file.

expected result table = 
	VAR _t = ADDCOLUMNS(
		Data,
		"@prevdate", MAXX(
			OFFSET(
				-1,
				Data,
				ORDERBY(
					Data[Date],
					ASC
				),
				,
				PARTITIONBY(Data[Phase]),
				MATCHBY(
					Data[Phase],
					Data[Date]
				)
			),
			Data[Date]
		)
	)
	VAR _condition = ADDCOLUMNS(
		_t,
		"@condition", IF(
			INT(Data[Date] - [@prevdate]) = 1,
			0,
			1
		)
	)
	VAR _group = ADDCOLUMNS(
		_condition,
		"@group", SUMX(
			WINDOW(
				1,
				ABS,
				0,
				REL,
				_condition,
				ORDERBY(
					Data[Date],
					ASC
				),
				,
				PARTITIONBY(Data[Phase]),
				MATCHBY(
					Data[Phase],
					Data[Date]
				)
			),
			[@condition]
		)
	)
	VAR _result = ADDCOLUMNS(
		SUMMARIZE(
			_group,
			Data[Phase],
			Data[Date],
			[@group]
		),
		"StartDate", MINX(
			FILTER(
				_group,
				Data[Phase] = EARLIER(Data[Phase]) && [@group] = EARLIER([@group])
			),
			Data[Date]
		),
		"EndDate", MAXX(
			FILTER(
				_group,
				Data[Phase] = EARLIER(Data[Phase]) && [@group] = EARLIER([@group])
			),
			Data[Date]
		)
	)

	RETURN
		SUMMARIZE(
			_result,
			Data[Phase],
			[StartDate],
			[EndDate]
		)

[ValtteriN · Accepted solution]

Hi,

Here is one way to do this. Due to the table not containing ids this took a bit of trial and error:

EVALUATE

VAR _vtable1 =

    ADDCOLUMNS(

        'Table (44)',

        "PreviousDate",

        VAR _phase = [Phase]

        VAR _date = [Date]

        VAR _previous =

            CALCULATE(

                MAX('Table (44)'[Date]),

                FILTER(

                    'Table (44)',

                    [Phase] = _phase && 'Table (44)'[Date] < _date

                )

            )

        RETURN IF(_previous = _date - 1, _previous, BLANK())

    )

-- Compute the start date for each continuous block

VAR _startdate =

    ADDCOLUMNS(

        _vtable1,

        "StartDate",

        IF(

            ISBLANK([PreviousDate]),

            [Date],

            BLANK()

        )

    )

-- Identify the end date by checking for gaps in the next date

VAR _enddate =

    ADDCOLUMNS(

        _startdate,

        "EndDate",

        VAR _phase = [Phase]

        VAR _date = [Date]

        VAR _next =

            CALCULATE(

                MIN('Table (44)'[Date]),

                FILTER(

                    'Table (44)',

                    [Phase] = _phase && 'Table (44)'[Date] > _date

                )

            )

        RETURN IF(_next <> _date + 1 || ISBLANK(_next), _date, BLANK())

    )

-- Summarize the result to get start and end dates for each continuous range

VAR _result =

    VAR _vtable2 = GROUPBY(_enddate, [Phase], [StartDate], [EndDate])

    RETURN

        ADDCOLUMNS(

            _vtable2,

            "edate",

            VAR _phase = [Phase]

            VAR calculatedEdate =

            var _sdate = [StartDate] RETURN

                CALCULATE(

                    MINX(FILTER(_vtable2, [Phase] = _phase && [EndDate]>_sdate), [EndDate])

                )

            RETURN

                IF(

                    calculatedEdate < [EndDate],

                    [EndDate],  -- Return the original EndDate if calculatedEdate is smaller

                    calculatedEdate  -- Otherwise, return calculatedEdate

                )

        )

RETURN

GROUPBY(FILTER(_result,NOT(ISBLANK([StartDate]))),[Phase],[StartDate],[edate])




End result shown in query view:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[tharunkumarRTK · Accepted solution]

@eng_123 

I think implementing this solution in power bi would be a much easier, please find the PBIX file attached.

Need a Power BI Consultation? Hire me on Upwork

Connect on LinkedIn

Did I answer your question? Mark my post as a solution!
If I helped you, click on the Thumbs Up to give Kudos. Proud to be a Super User!