Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get 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

Reply
fabriciosnn22
New Member

Sequence INDEX/EALIER DAX or Power Query

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

 

EmployeeDateIndex
Jhonny05/02/2021  1
Jhonny06/02/2021  2
Jhonny07/02/2021  3
Jhonny08/02/2021  4
Jhonny09/02/2021  5
Jhonny10/02/2021  6
Jhonny11/02/2021  7
Jhonny15/02/2021  1
Jhonny16/02/2021  2
Jhonny19/02/2021  1
Jhonny20/02/2021  2

 

1 ACCEPTED SOLUTION
CNENFRNL
Community Champion
Community Champion

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
    )

CNENFRNL_0-1647206150098.png

 

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"

CNENFRNL_1-1647207268963.png


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!

View solution in original post

3 REPLIES 3
fabriciosnn22
New Member

Thank very much 👏👏

CNENFRNL
Community Champion
Community Champion

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
    )

CNENFRNL_0-1647206150098.png

 

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"

CNENFRNL_1-1647207268963.png


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 😀

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors