Create a "Day Number" column

[jtlrlfrc]

Hi everyone,

I would like to be able to create a "Day Number" column. More specifically, our app at the end of each day sends the user a push notification with a snapshot of their app activity for the day during the first week of app use. I would like to be able to create a column that takes the values for this event and categorize it into "Day One", "Day Two", "Day Three" for each user so that we can see how the user's activity changes as the days progress. Since every user's first, second, third etc. day of use is different, I've had a tough time navigating this issue.

Here is a quick example of the Day Number column I'd like to create:

User ID	Date	End of Day Value	Day Number
001	August 1	1	Day One
002	August 1	3	Day One
001	August 2	6	Day Two
003	August 2	2	Day One
002	August 2	5	Day Two
002	August 3	8	Day Three
001	August 4	10	Day Three
003	August 4	7	Day Two
003	August 5	9	Day Three

Would anyone have an idea how to do this?

Thanks in advance!

[v-sihou-msft · Accepted solution]

@jtlrlfrc

In this scenario, your "Day Number" column is actually a rank column based on "End Of Day Value" group on UserID. You can add a calculated column like below:

Day Number =
RANKX (
    ALL ( Table1[Date] ),
    CALCULATE (
        SUM ( Table1[End of Day Value] ),
        ALLEXCEPT ( Table1, Table1[User ID], Table1[Date] )
    ),
    ,
    ASC,
    DENSE
)
    - 1

Regards,