Average per unique item per day per year

[Anonymous]

I must calculate number of unique items per day/365 days

My columns are:

[Fact table(Item Number)]

[Fact table(Start Date)]

Item Number	Start Date
E170	10/12/2020
E127	12/12/2020
C742	23/12/2020
E127	26/11/2020
A435	05/11/2020
F732	19/12/2020
E127	17/12/2020
E127	12/12/2020
E170	12/12/2020

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create relationships between the tables.

Then create a measure.

Measure =
VAR days_ =
    DATEDIFF (
        MINX ( ALL ( 'Table 2' ), 'Table 2'[Date] ),
        MAX ( 'Table'[Start Date] ),
        DAY
    )
VAR _Distinct =
    DISTINCTCOUNT ( 'Table'[Item Number] )
RETURN
    DIVIDE ( _Distinct, days_ )

If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Based on the data that you have shared, please show the expected result.

[AlexisOlson]

This would average the items per day for all dates that appear in the Start Date column.

AvgItems =
AVERAGEX (
    VALUES ( Fact[Start Date] ),
    DISTINCTCOUNT ( Fact[Item Number] )
)

If you need something different, please specify how to treat dates that don't appear in your fact table and whether you want to partition by year or not, etc.

[Anonymous]

Hi @AlexisOlson 
I actually need to rephrase my question. 
This is what I must calculate: 
I must calculate the average number of usage.
How many times the unique number has been used per day over 2 years and 2 months. 

[Fact table(Item Number)]

[Fact table(Start Date)]

My calculations are based on 797 days (2 years and 2 months from Jan 2020 to end of Feb 2022)
In total I have 1.266,929 [Fact table(Item Number)]

but 4,165 distinct Item numbers (that distinct number was calculated as a measure:

Distinct Item numbers = DISTINCTCOUNT('Fact table'[Item number])

Your help would be very uch appreciated. 
Best,
Jola