Enter an unique index to each day

[Anonymous]

Hi guys,

i have the following table, datetime and a value, this pic shows only ne day August 7th however it has much more, 

I would like to get a cummulative count of each unique day in the table, for example August 7th gets 1, without caring for the time, then August 8th the 2 and so on, how can i achieve this?

Many thanks.

[MattAllington · Accepted solution]

You should split the date/time into 2 columns during data load. You should also get a calendar table for more robust reporting. https://exceleratorbi.com.au/power-pivot-calendar-tables/

[v-shex-msft · Accepted solution]

HI @Anonymous,

You can try to use following calculate column formula if it meets your requirement:

Index=
COUNTROWS (
    SUMMARIZE (
        FILTER (
            ADDCOLUMNS ( ALLSELECTED ( Table ), "Date", DATEVALUE ( Table[EndDateTime] ) ),
            [Date] <= EARLIER ( Table[EndDateTime] )
        ),
        [Date]
    )
)

Regards,

Xiaoxin Sheng

[pranit828]

Hi @Anonymous 

I would use the  DATEDIFF('table'[EndDateTime],today(),DAY) and create a column, sounds good. 

Did I resolve your issue? Mark my post as a solution! Appreciate your Kudos, Press the thumbs up button!!

Regards,
Pranit

[MattAllington · Accepted solution]

You should split the date/time into 2 columns during data load. You should also get a calendar table for more robust reporting. https://exceleratorbi.com.au/power-pivot-calendar-tables/