- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Create start date and end date from date
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 |
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
|
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Here is one way to do this. Due to the table not containing ids this took a bit of trial and error:
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/
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
Expertise = List.Accumulate( {Days as from Today}, {Skills and Knowledge}, (Current, Everyday) => Current & Day.LeanAndPractise(Everyday) ) |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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,
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Here is one way to do this. Due to the table not containing ids this took a bit of trial and error:
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/
Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
|
Helpful resources
Join us at the Microsoft Fabric Community Conference
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Microsoft Fabric Community Conference 2025
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
Subject | Author | Posted | |
---|---|---|---|
Anonymous
| 12-08-2023 11:02 AM | ||
01-02-2023 01:24 AM | |||
05-21-2024 01:01 AM | |||
10-05-2023 05:14 AM | |||
07-17-2024 09:45 PM |
User | Count |
---|---|
21 | |
18 | |
17 | |
7 | |
5 |
User | Count |
---|---|
31 | |
27 | |
19 | |
13 | |
12 |