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 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? 🙂

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

5 Replies

  • Mauro89's avatar
    Mauro89
    Super User
    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

    • FabvE's avatar
      FabvE
      Helper I
      8 months ago

      Hi,

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

      • Mauro89's avatar
        Mauro89
        Super User
        8 months ago

        Hi FabvE,

         

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

         

        Best regards!

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