March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Helo colleagues
I need to create a column with a sequence based on the date column, where it starts again when there are a break in the sequence of dates, as in this example in MS Excel.
Could you help ?
Thank You
Employee | Date | Index |
Jhonny | 05/02/2021 | 1 |
Jhonny | 06/02/2021 | 2 |
Jhonny | 07/02/2021 | 3 |
Jhonny | 08/02/2021 | 4 |
Jhonny | 09/02/2021 | 5 |
Jhonny | 10/02/2021 | 6 |
Jhonny | 11/02/2021 | 7 |
Jhonny | 15/02/2021 | 1 |
Jhonny | 16/02/2021 | 2 |
Jhonny | 19/02/2021 | 1 |
Jhonny | 20/02/2021 | 2 |
Solved! Go to Solution.
Index =
VAR __dt = DATA[Date]
VAR __tb =
FILTER(
DATA,
DATA[Employee] = EARLIER( DATA[Employee] )
&& DATA[Date] < __dt
)
VAR __streak =
ADDCOLUMNS(
__tb,
"@contiguous",
VAR __d = DATA[Date]
VAR __prev =
MAXX( FILTER( __tb, DATA[Date] < __d ), DATA[Date] )
RETURN
__prev + 1 = __d
)
RETURN
IF(
MAXX( __tb, DATA[Date] ) + 1 = __dt,
__dt - MAXX( FILTER( __streak, NOT [@contiguous] ), DATA[Date] ) + 1,
1
)
It's easier to handle in PQ,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srIz8urVNJRMtI31TcyMDJUitVBETXDKmqOVdQCq6glVlFDA+zChtiFsTvOELvrDLFbaYRkZUREBFDMWB9snRGKkBGmEMSxQLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type date}}),
Grouped = Table.Group(#"Changed Type", "Employee", {"grp", each let l=[Date], index = List.Accumulate({1..List.Count(l)-1}, {1}, (s,c) => if Duration.Days(l{c}-l{c-1})=1 then s&{List.Last(s)+1} else s&{1}) in Table.FromColumns(Table.ToColumns(_) & {index}, {"Empl", "Date", "Index"})}),
#"Expanded grp" = Table.ExpandTableColumn(Grouped, "grp", {"Date", "Index"}, {"Date", "Index"})
in
#"Expanded grp"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank very much 👏👏
Index =
VAR __dt = DATA[Date]
VAR __tb =
FILTER(
DATA,
DATA[Employee] = EARLIER( DATA[Employee] )
&& DATA[Date] < __dt
)
VAR __streak =
ADDCOLUMNS(
__tb,
"@contiguous",
VAR __d = DATA[Date]
VAR __prev =
MAXX( FILTER( __tb, DATA[Date] < __d ), DATA[Date] )
RETURN
__prev + 1 = __d
)
RETURN
IF(
MAXX( __tb, DATA[Date] ) + 1 = __dt,
__dt - MAXX( FILTER( __streak, NOT [@contiguous] ), DATA[Date] ) + 1,
1
)
It's easier to handle in PQ,
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8srIz8urVNJRMtI31TcyMDJUitVBETXDKmqOVdQCq6glVlFDA+zChtiFsTvOELvrDLFbaYRkZUREBFDMWB9snRGKkBGmEMSxQLFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Employee = _t, Date = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Employee", type text}, {"Date", type date}}),
Grouped = Table.Group(#"Changed Type", "Employee", {"grp", each let l=[Date], index = List.Accumulate({1..List.Count(l)-1}, {1}, (s,c) => if Duration.Days(l{c}-l{c-1})=1 then s&{List.Last(s)+1} else s&{1}) in Table.FromColumns(Table.ToColumns(_) & {index}, {"Empl", "Date", "Index"})}),
#"Expanded grp" = Table.ExpandTableColumn(Grouped, "grp", {"Date", "Index"}, {"Date", "Index"})
in
#"Expanded grp"
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
Thank you very much 😀
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
62 | |
30 | |
25 | |
22 | |
17 |