Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 nu...
14 day
rolling
sum
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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.