topic Re: Create start date and end date from date in DAX Commands and Tips

Create start date and end date from date

eng_123 — Sun, 13 Oct 2024 03:51:50 GMT

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

Re: Create start date and end date from date

tharunkumarRTK — Sun, 13 Oct 2024 06:53:42 GMT

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!

Re: Create start date and end date from date

ValtteriN — Sun, 13 Oct 2024 06:57:16 GMT

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/

Re: Create start date and end date from date

Jihwan_Kim — Sun, 13 Oct 2024 07:05:35 GMT

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

Re: Create start date and end date from date

DataNinja777 — Sun, 13 Oct 2024 09:32:16 GMT

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,

Re: Create start date and end date from date

ThxAlot — Sun, 13 Oct 2024 19:43:01 GMT

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"