numbering rows by days of the month

[pani_victoria]

Colleagues, hello!

I have a column with a period and a number of rows with data that equals the number of days in a month.

How can I put days from 1 to 28-31 in a separate column?

[FarhanJeelani · Accepted solution]

Hi @pani_victoria ,

To achieve this in Power BI, you can create a calculated column that assigns a day number (1 to 28/31) to each row within the month.

Solution in Power BI:

1. Create a Calculated Column: Use the following DAX formula to create a new column for days:

   DAX

DayNumber = 
RANKX(
    FILTER(
        TableName,
        TableName[Period] = EARLIER(TableName[Period])
    ),
    TableName[IndexColumn],
    ,
    ASC
)​

• Replace TableName with your table's name.
• Replace IndexColumn with a unique column for each row (e.g., row ID or an existing sequential column). If you don’t have one, you can add an Index column in Power Query.

• Result: This formula will rank rows within each month, creating day numbers 1 to 28/31 in a new column.

Please mark this as solution if it helps. Appreciate Kudos.

[pani_victoria]

Thank you!

It works!)
but not for me

I didn't take into account some input data, because of which this solution doesn't work correctly