Forum Discussion
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:
Can anyone help with this?
Many thanks in advance
SEE FILE ATTACHED
https://www.dropbox.com/s/vzbztfgwq9370nq/Notification%20vs.%20Epi.pbix?dl=0
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
2 Replies
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
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.
