Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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!
Solved! Go to 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
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
Hi,
Sure, that one is working, thank you!
Also, this one:
NumOfDays2 = CALCULATE ( DISTINCTCOUNT ( 'Date'[Date] ); FILTER ( 'Date'; Sales[Sales Amount] > 0 ) )