Incremental counter per day

[FabvE]

Hi,

I'm struggling to create an extra column to have a incremental counter which resets per day. My expected solution should look like this:

Date		Counter
01.12.2025		1
01.12.2025		2
01.12.2025		3
01.12.2025		4
02.12.2025		1
02.12.2025		2
02.12.2025		3
03.12.2025		1
03.12.2025		2

I should note that I use PQ within Excel.

How can I achieve this? 🙂

[Zanqueta]

Hi @FabvE,

That´s is my solution:

Why this approach?

• It is simple and scalable because it uses Grouping + Indexing rather than row-by-row calculations.
• It performs well for large datasets as Power Query processes these operations efficiently.

Script M to reproduce the behaviour.

let
    // Source: sample data with the dates provided
    Source = Table.FromRows({
        {"01.12.2025"},
        {"01.12.2025"},
        {"01.12.2025"},
        {"01.12.2025"},
        {"02.12.2025"},
        {"02.12.2025"},
        {"02.12.2025"},
        {"03.12.2025"},
        {"03.12.2025"}
    }, {"Date"}),

    // Convert to Date type (format dd.MM.yyyy)
    ChangeType = Table.TransformColumnTypes(Source, {{"Date", type date}}),

    // Sort by Date
    Sorted = Table.Sort(ChangeType, {{"Date", Order.Ascending}}),

    // Group by Date and add an index within each group
    Grouped = Table.Group(Sorted, {"Date"}, {
        {"All        {"AllData", each Table.AddIndexColumn(_, "Counter", 1, 1, Int64.Type)}
    }),

    // Expand back to original format
    Expanded = Table.ExpandTableColumn(Grouped, "AllData", {"Date", "Counter"})
in
Expanded 

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

[Mauro89 · Accepted solution]

Hi @FabvE,

You can create this counter using a calculated column in Power BI. Here's the DAX formula:

Counter = 
RANKX(
    FILTER(
        'YourTable',
        'YourTable'[Date] = EARLIER('YourTable'[Date])
    ),
    'YourTable'[Date],
    ,
    ASC,
    DENSE
)

If you need it based on a specific sort order (like timestamp within the day):

Counter = 
VAR CurrentDate = 'YourTable'[Date]
VAR CurrentRow = 'YourTable'[YourUniqueID]  // or timestamp column
RETURN
COUNTROWS(
    FILTER(
        'YourTable',
        'YourTable'[Date] = CurrentDate &&
        'YourTable'[YourUniqueID] <= CurrentRow
    )
)

In Power Query (alternative):

1. Sort by Date column
2. Group by Date, add an "All Rows" aggregation
3. Add custom column: Table.AddIndexColumn([AllRows], "Counter", 1, 1)
4. Expand the nested table

The Power Query approach is more efficient for large datasets as it's computed during refresh rather than row-by-row like calculated columns.

Best regards!

PS: If you find this post helpful consider leaving kudos or mark it as solution

[FabvE]

Hi,

thx for the post. I forgot to mention that I use PQ in Excel not PowerBI...

[Mauro89]

Hi @FabvE,

ok but no worries. Then check out if the Power Query alternative I mentioned works for you.

Best regards!

[FabvE]

Yes, that worked perfectly. Thank you!!!!