Monthly Average Distinct Count - Measure

[mariajuliao]

How can I create a measure that calculates the  monthly average of distinct count

CaseNumber	CreatedDate	Computer
abcde	1/2/2023	Computer1
abcdf	1/3/2023	Computer1
abcdg	1/4/2023	Computer2
abcdh	2/14/2023	Computer2
abcdi	2/17/2023	Computer1

In this example, for month 1 I have three cases. For month 2 I have two cases. The monthly average is 2.5 cases.

How do I create a measure to calculate this? 

Additionally, how do I create a measure to calculate the average per month, per computer?

[CNENFRNL]

[FreemanZ]

hi @mariajuliao 

try like:

Measure =

VAR _table=

ADDCOLUMNS(

     TableName,

     "YYYYMM",

     FORMAT(TableName[CreatedDate], "YYYYMM")

)

RETURN

AVERAGEX(

    SUMMARIZE(_table, [YYYYMM]),

    CACULATE(COUNTROWS(_table))

)