14 day rolling sum

[Anonymous]

Apologies in advance if this is confusing:

I have 2 dates: Notification date and Epi Date

What im trying to do is get a rolling 14 day sum of each. I have tried this but it keeps giving me numbers that are way off:

14 Day Rolling Average - Notification Date =

CALCULATE(SUM('Sheet1'[Epi Date]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),14,DAY))

/

CALCULATE(DISTINCTCOUNT('Sheet1'[Event Date]),DATESINPERIOD('Sheet1'[Event Date],LASTDATE('Sheet1'[Event Date]),14,DAY))

Can anyone help with this?

Many thanks in advance

SEE FILE ATTACHED

https://www.dropbox.com/s/vzbztfgwq9370nq/Notification%20vs.%20Epi.pbix?dl=0

[amitchandak]

@Anonymous , Join event Date with Dat table and create a measure like

Rolling 14 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-14,Day))

Rolling 14 day = CALCULATE(sum('Table'[Number]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),14,Day))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[mahoneypat · Accepted solution]

Here is one of the measure expressions to be used in a Table visual with your Event Date column.  You can adapt it to also work with your Epi Date column too.

Prev 14 Day Event Count= var thisdate = MIN(Sheet1[Event Date])

return CALCULATE(COUNT(Sheet1[Epi Date]), FILTER(ALL(Sheet1[Event Date]), Sheet1[Event Date]<=thisdate && Sheet1[Event Date]>=thisdate-13))

Regards,

Pat