topic Sequence INDEX/EALIER DAX or Power Query in DAX Commands and Tips

Sequence INDEX/EALIER DAX or Power Query

fabriciosnn22 — Sun, 13 Mar 2022 18:08:44 GMT

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

Re: Sequence INDEX/EALIER DAX or Power Query

CNENFRNL — Sun, 13 Mar 2022 21:35:59 GMT

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"

Re: Sequence INDEX/EALIER DAX or Power Query

fabriciosnn22 — Sun, 13 Mar 2022 22:28:26 GMT

Thank very much 👏👏

Re: Sequence INDEX/EALIER DAX or Power Query

fabriciosnn22 — Sun, 13 Mar 2022 22:49:25 GMT

Thank you very much 😀