cancel
Showing results for
Did you mean:
Helper III

## 14 day rolling sum

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?

SEE FILE ATTACHED

1 ACCEPTED SOLUTION
Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

2 REPLIES 2
Super User IV

@Niiru1 , 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.

Proud to be a Super User!

Super User IV

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

Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!