Average Daily Count

[rawiswarden]

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")

Date	Total
12/1/2019	1205
12/2/2019	1205
12/3/2019	1205
12/4/2019	1204
12/5/2019	1205
12/6/2019	1205
12/7/2019	1205
12/8/2019	1205
12/9/2019	1205
12/10/2019	1207
12/11/2019	1206
12/12/2019	1206
12/13/2019	1206
12/14/2019	1206
12/15/2019	1201
12/16/2019	1201
12/17/2019	1202
12/18/2019	1201
12/19/2019	1200
12/20/2019	1203
12/21/2019	1201
12/23/2019	1201
12/24/2019	1203
12/25/2019	1203
12/26/2019	1203
12/27/2019	1203
12/28/2019	1200

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

[Greg_Deckler]

Source data would help. Why is 12/22 missing? Are there no related records for that day?

I would try recreating your table visualization as a VAR and then do an AVERAGEX across it.

[rawiswarden]

This data comes from a temporal table.  Sometimes errors in the ETL process occurs, so we do not have data for that day.  It should be rare that it happens, but it does happen.  I will try your suggestion 🙂

[Greg_Deckler · Accepted solution]

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])

[amitchandak]

If you need to do average after grouping data (Count of data) , values can come handy

Count_measure =  You have already defined

Daily Average = AVERAGEX(values(Date[date]),[count_measure])

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601