Define the granularity for "day" when calculating NumOfDays?

[webportal]

Hello,

When calculating the average sales per day, I have the following measure for NumOfDays:

NumOfDays = IF (
    [Sales Amount] > 0; 
    COUNTROWS ( Date )
)

What this is doing is removing the number of days for those dates when there are no sales.

Thus, I have the following visual:

As you can see, the total is wrong. 

This is due to the fact that the database has more years than those shown in the matrix. 

How can I define the granularity for "day" when calculating NumOfDays?

That is, how can I count the rows for those days with sales only?

Thanks!

[v-qiuyu-msft · Accepted solution]

Hi @webportal,

In your scenario, you can create a measure like below:

NumOfDays = CALCULATE(COUNTROWS('Adventure Works'),FILTER('Adventure Works',SUM('Adventure Works'[Internet Sales Amount])>0))

Best Regards,
Qiuyun Yu

[webportal]

Hi,

Sure, that one is working, thank you!

Also, this one:

NumOfDays2 =
CALCULATE (
    DISTINCTCOUNT ( 'Date'[Date] );
    FILTER ( 'Date'; Sales[Sales Amount] > 0 )
)