Average of Cases created daily over the last 21 days

[jdubs]

Hello,

I am trying to create a measure to show a count of the average case created count per day over the last 21 days. I had Copilot help with a calulation but it is not returning any data. Any help appreciated

Created_21avg = VAR __LAST_DATE = LASTDATE('Cases'[createdon].[Date]) RETURN AVERAGEX(DATESBETWEEN('Cases'[createdon].[Date], DATEADD(__LAST_DATE, -21, DAY), __LAST_DATE), CALCULATE(COUNTROWS('Cases')))

[Anonymous · Accepted solution]

Hi @jdubs ,

Try to modify your formula like below:

Created_21avg = 
VAR __LAST_DATE =
    TODAY () - 1 -- Yesterday's date
RETURN
    AVERAGEX (
        DATESBETWEEN ( 'Cases'[createdon].[Date], __LAST_DATE - 21, __LAST_DATE ),
        CALCULATE ( COUNTROWS ( 'Cases' ) )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jdubs]

Hey Adamk,

Thank you. I think this is working! Just need to spot-check some data and will mark as solved!

[jdubs]

Hi Greg,

I'm getting a syntax error which I thought was due to the double quotes, which I changed to single quotes, but it still doesn't seem to like [_Count] in the formula.

Alternatively, I tried this formula which actually does return data however it only ever returns "1" no matter what i change the day range to e.g. 21 vs. 5

21Day_avg = AVERAGEX(KEEPFILTERS(VALUES('Cases'[createdon])), CALCULATE(COUNTROWS('Cases'), KEEPFILTERS('Cases'[createdon] > NOW() - 21)))

[Greg_Deckler]

@jdubs Try:

Created_21avg = 
  VAR __LastDate = MAX( 'Cases'[createdon] )
  VAR __MinDate = __LastDate - 21
  VAR __Table = 
    SUMMARIZE( 
      FILTER( 'Cases', 'Cases'[createdon] >= __MinDate && 'Cases'[createdon] <= __LastDate )
      [createdon], 
      "__Count", COUNTROWS( 'Cases' ) 
    )
  VAR __Result = AVERAGEX( __Table, [__Count] )
RETURN
  __Result