Create start date and end date from date

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!

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/

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

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"