cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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?

Many thanks in advance

SEE FILE ATTACHED

1 ACCEPTED SOLUTION
Employee

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!

@mahoneypa HoosierBI on YouTube

2 REPLIES 2
Super User

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

Employee

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!

@mahoneypa HoosierBI on YouTube

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors