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...
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])
rawiswarden's avatar
rawiswarden
Helper I
6 years ago

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's avatar
Greg_Deckler
Community Champion
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])