Define the granularity for "day" when calculating NumOfDays?

Question

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!

Answer 1

I recommend two things:

1. Use CALCULATE() instead of IF, which is the proper DAX way of defining measures with filters
2. Use DISTINCTCOUNT() instead of COUNTROWS(), to ensure you never run into double counting (in case your data ever becomes more granular)

---

This calculation may work - but you may need to specify the actual date column inside your Date table.

NumOfDays = CALCULATE(DISTINCTCOUNT(Date[Date]),FILTER(SalesTable,[Sales Amount] > 0))

If there is more to the problem, let me know what exactly you are expecting to see in your matrix.