Daily Average and Relative Month filter problem

[wildmight2017]

Good morning,

I have the following issue. I'm trying to create a very basic measure.

Avg Daily Sales = DIVIDE (SUM(Sales), DISTINCTCOUNT(Date[Date]) ) 

Works as expected, but when using a Relative Filter ( Date = This Month)  - the count of dates switches to 31 for August, hence the issue in formula. Using  a combination of CALCULATE and FILTER, such as CALCULATE(DISTINCTCOUNT(Date[Date]), FILTER(ALLSELECTED(Date[Date]), Date[Date]<TODAY() ) , I can solve this for the current month's measure, however, the formula stops working correctly for a filter with Previous 3 / 6 / etc. months - because it aggregates the count of dates for the whole period (181 days for 6 months). 

Is there an elegant solution that would avoid me having to spell out 2 measure for different tables? 

Thank you

[v-juanli-msft · Accepted solution]

Hi @wildmight2017

Do you have values in Sales after today?

I test on my site, If my Sales is not 0 after today, when I use your first measure and select a Relative Filter ( Date = This Month), It would calculate dates until 31 August.

Best Regards

Maggie