Forum Discussion

FabvE's avatar
FabvE
Helper I
8 months ago
Solved

Incremental counter per day

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...
Need Help
  • Mauro89's avatar
    Mauro89
    8 months ago

    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

Zanqueta's avatar
Zanqueta
Super User
8 months ago

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 🌀.