How can I get an average calculation to work with a relative date filter?

[kkirner]

Hi, I have a pretty simple DAX for average closed files. 

Average Closings per Day =

AVERAGEX(

VALUES(SPSRevenue[DatePaid]),

CALCULATE(_MeasuresNonCommission[Closed File Count]))

I enter it into a card w/ the DatePaid filter set to relative date for is in this month - I get "blank" for my answer, when I set the filter for is in the last calendar month, or is in the current year, I seem to get the right answer.

Any idea what would cause the "blank" answer?

[kkirner]

@tamerj1 I appreciate the quick reply and yes, I should've put that code in the first thread.  Here you go:

Closed File Count =

CALCULATE([distinctRevenueCount], SPSRevenue[BillCode]="TP")

We do have closed files this month.  The card below is built on the Closed File Count measure, with the DatePaid filter using the relative date of Is in This Month.

[tamerj1]

@kkirner 
Agian you have [distinctRevenueCount] measure. What is the code of this measure?

[kkirner]

Sorry about that...here is that measure:

distinctRevenueCount =

var orderCount = DISTINCTCOUNT(SPSRevenue[OrderNumber])

return

IF( orderCount > 0, VALUE(orderCount), IF(orderCount = 0, VALUE(0)))

[tamerj1]

@kkirner 
Are you sure you have orders in this month with SPSRevenue[BillCode]="TP"?

[kkirner]

Yes.  We have revenue every day.  Here's the count from today...it is actually revenue that generates the count.  In other words, we don't count the file until there is revenue realized from it.

[kkirner · Accepted solution]

@tamerj1 I have to apologize.  I truly made a rookie mistake here.  I never refreshed the data on the desktop version of the report.  I can't even believe I did that, but I did.  I refreshed it after my last reply and I now have data where it previously said "blank".  Thank you for your time and offer to help.

[tamerj1]

@kkirner 
Maybe there is no data for "this month"? What is the code of the [Closed File Count] measure?