Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
EZiamslow
Helper II
Helper II

Count Nth # of Occurrence

Can you someone help me count # of occurrence by date? Please see the date below.

 

Original Data:

IDDate
AAA9/28/19 1:00 AM
BBB9/28/19 2:00 AM
AAA9/28/19 3:00 AM
AAA9/28/19 4:00 AM
CCC9/28/19 5:00 AM
CCC9/29/19 1:00 AM

 

Adding # of Occurrence column to count IDs # of times appear on the data table.

IDDate# of occurrence
AAA9/28/19 1:00 AM1
BBB9/28/19 2:00 AM1
AAA9/28/19 3:00 AM2
AAA9/28/19 4:00 AM3
CCC9/28/19 5:00 AM1
CCC9/29/19 1:00 AM2

 

When I filter AAA, I like to see the following.

IDDate# of occurrence
AAA9/28/19 1:00 AM1
AAA9/28/19 3:00 AM2
AAA9/28/19 4:00 AM3

 

I couldn't find by searching online. Please help. Thanks!

 

 

 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @EZiamslow 

Try this

1. Create a column with only the date (excluding time):

DateOnly = INT(Table1[Date])

2. Create the occurrence column:

# of occurrence =
CALCULATE (
    COUNT ( Table1[ID] ),
    ALLEXCEPT ( Table1, Table1[ID], Table1[DateOnly] ),
    Table1[Date] <= EARLIER ( Table1[Date] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

View solution in original post

1 REPLY 1
AlB
Super User
Super User

Hi @EZiamslow 

Try this

1. Create a column with only the date (excluding time):

DateOnly = INT(Table1[Date])

2. Create the occurrence column:

# of occurrence =
CALCULATE (
    COUNT ( Table1[ID] ),
    ALLEXCEPT ( Table1, Table1[ID], Table1[DateOnly] ),
    Table1[Date] <= EARLIER ( Table1[Date] )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.