Average distinct count of date field

[jamesonct]

Hi all,

Here's what I'm trying to do:

I want to create a mesaure to get the distinct count of a column called 'Item Data'[Item Status].  With that distinct count, I want to average the number of items received per week by a column called 'Item Data'[Creation Date] for the last 365 days.

Can someone help me out with this?

Thank you!

[jamesonct]

So lets say I do this:

I think I may have mispoke.  I believe I want count not distinct count.  I'm still learning.

AvgNumberItemsWeek = COUNT('Item Data'[Item Status])

And filter Creation Date by relative date in the last 365 days, I get 2118 count of item status.  What I want to do is take that count and then average it out to items per week.

The data I'm working with looks like this:

[ryan_mayu]

it's better to provide some dummy data. if the solution works, then apply it to your real data. Otherwise it's very hard to provide the proper solution

[jamesonct]

Got it.  Here's some dummy data:

Item Code

Lab Case Number

Submitting Agency

Incident/Case Number

Case Officer

Case Type

Item Type

Item Status

Location

Item Description

Danger Flag

Creation Date

Modified Date

T1234

TEST-2024-00001

Generic Agency Name

123345

J. Doe

N/A

USB Flash Drive

Pending Analysis

Loc1

Item 1

'- Not Dangerous -

3/28/2024 15:08

3/28/2024 15:08

T1235

TEST-2024-00002

Generic Agency Name

123345

J. Doe

N/A

SIM Card

Pending Return to Department

Loc2

Item 2

'- Not Dangerous -

3/28/2024 14:51

3/28/2024 14:51

T1236

TEST-2024-00003

Generic Agency Name

123345

J. Doe

N/A

USB Flash Drive

Pending Analysis

Loc3

Item 3

'- Not Dangerous -

3/28/2024 13:50

3/28/2024 14:19

T1237

TEST-2024-00004

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc4

Item 4

'- Not Dangerous -

3/28/2024 13:31

3/28/2024 13:35

T1238

TEST-2024-00005

Generic Agency Name

123345

J. Doe

N/A

Tablet

Pending Analysis

Loc5

Item 5

'- Not Dangerous -

3/28/2024 12:17

3/28/2024 12:42

T1239

TEST-2024-00006

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc6

Item 6

'- Not Dangerous -

3/28/2024 12:17

3/28/2024 12:39

T1240

TEST-2024-00007

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc7

Item 7

'- Not Dangerous -

3/28/2024 12:17

3/28/2024 12:39

T1241

TEST-2024-00008

Generic Agency Name

123345

J. Doe

N/A

Tablet

Pending Analysis

Loc8

Item 8

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1242

TEST-2024-00009

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc9

Item 9

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1243

TEST-2024-00010

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc10

Item 10

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1244

TEST-2024-00011

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc11

Item 11

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1245

TEST-2024-00012

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc12

Item 12

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1246

TEST-2024-00013

Generic Agency Name

123345

J. Doe

N/A

Other

Pending Analysis

Loc13

Item 13

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1247

TEST-2024-00014

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc14

Item 14

'- Not Dangerous -

3/28/2024 12:16

3/28/2024 12:39

T1248

TEST-2024-00015

Generic Agency Name

123345

J. Doe

N/A

Phone

Pending Analysis

Loc15

Item 15

'- Not Dangerous -

3/28/2024 12:11

3/28/2024 12:40

T1249

TEST-2024-00016

Generic Agency Name

123345

J. Doe

N/A

Computer

Pending Analysis

Loc16

Item 16

'- Not Dangerous -

3/28/2024 9:04

3/28/2024 9:07

T1250

TEST-2024-00017

Generic Agency Name

123345

J. Doe

N/A

USB Flash Drive

Checked Out

Loc17

Item 17

'- Not Dangerous -

3/28/2024 8:24

3/28/2024 12:03

T1251

TEST-2024-00018

Generic Agency Name

123345

J. Doe

N/A

USB Flash Drive

Pending Return to Department

Loc18

Item 18

'- Not Dangerous -

3/28/2024 5:49

3/28/2024 5:56

[ryan_mayu]

what's the expected output based on the dummy data you provided?

[jamesonct]

1) Get a count of the rows

2) Average that count using the 'Creation Date' column for the last 180 days.

[ryan_mayu]

could you pls proivde some sample data and expected output?