Forum Discussion

rawiswarden's avatar
rawiswarden
Helper I
6 years ago
Solved

Average Daily Count

Hello,

 

I have read a lot of threads on calculating an average daily count.  But I cannot get it right.  Any help is appreciated 🙂

 

I have a table that contains a daily count.   I am trying to create an average measure, so when the aggregation changes from daily to monthly or yearly the number reflects the average count for that period.

 

Total is a measure defined as 

 

CALCULATE(count(Dimension_AMS[ActiveLoan]), USERELATIONSHIP(Calendar_Dates[Date],Facts_AMS[Date]), Filter(Facts_AMS, Facts_AMS[Loan.StartTime] = Facts_AMS[Loan.StartTime]), Dimension_AMS[ActiveLoan] = "Y")

 

DateTotal
12/1/20191205
12/2/20191205
12/3/20191205
12/4/20191204
12/5/20191205
12/6/20191205
12/7/20191205
12/8/20191205
12/9/20191205
12/10/20191207
12/11/20191206
12/12/20191206
12/13/20191206
12/14/20191206
12/15/20191201
12/16/20191201
12/17/20191202
12/18/20191201
12/19/20191200
12/20/20191203
12/21/20191201
12/23/20191201
12/24/20191203
12/25/20191203
12/26/20191203
12/27/20191203
12/28/20191200

 

I am trying to create another measure that counts the number rows in the table to divide the measure by.  As you can see in the table above, 12/22/2019 is missing.  I cannot figure out how to create the day counter that changes based on the context of the table.

 

So in the above table, I would sum the total (32,497) and divide by the count (27) to get an average of 1203.59.  But I keep getting counts that include 12/22/2019, since my date table has that date.

 

I used distinctcountnoblank but it just resulted in counting all the dates and putting the same count in every row.  I couldn't figure out how to use distinctcountnoblank at the row level in the table, so it would adjust as the date changes (from daily to monthly).

 

Any help is appreciated!

 

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago

    I recreated this sort of. See attached, but I create a Date table and then a sample table with the missing date in it. You will need to filter your VAR table for NOT(ISBLANK()). Here are the measures I created for testing:

     

    Measure 6 = COUNTROWS(RELATEDTABLE(Table11))

Measure 7 = 
    VAR __Table = ADDCOLUMNS(ALLSELECTED('Table11Date'),"Measure",[Measure 6])
RETURN
    COUNTROWS(FILTER(__Table,NOT(ISBLANK([Measure]))))

Measure 8 = 
    VAR __Table = ADDCOLUMNS(ALLSELECTED('Table11Date'),"Measure",[Measure 6])
RETURN
    AVERAGEX(FILTER(__Table,NOT(ISBLANK([Measure]))),[Measure])

4 Replies