cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Frequent Visitor

Daily Rolling Sum Not Continuing After Blank Dates

I tried this DAX formula I found on the web to calculate a rolling 7 day sum. However for Austin there is no records past 9/9, so you would expect the running sum to continue 6 days after 9/9 but it goes to zero once there are no records to calulate. I included the sum of cases right next to the rolling sum for reference. Please let me know what other info is needed. Thank you.

1 ACCEPTED SOLUTION
Community Support

Hi @MickyG

you can try this,

``````Measure =
var _currentDate=MIN('Table'[date])
var _startDate=_currentDate-7
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[date]>_startDate&&'Table'[date]<=_currentDate))``````

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Hi @MickyG

you can try this,

``````Measure =
var _currentDate=MIN('Table'[date])
var _startDate=_currentDate-7
return CALCULATE(SUM('Table'[value]),FILTER(ALL('Table'),'Table'[date]>_startDate&&'Table'[date]<=_currentDate))``````

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Super User

even though i can't see a break in your data, its best practice to use a date table to ensure sure its continious when using date centred measures, something is broken it seems  and its hard to know without looking at your data?

can you create a date table and link  to your table and use the date fields from the date table

here is a good example of the pattern

https://www.sqlbi.com/articles/rolling-12-months-average-in-dax/

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Frequent Visitor

You are correct Vanessa, I linked it to my dates table instead of the report date. That took care of the spaces in between but The rolling sum still isn't continuing it's calcu;ation after a date with "0" records. I included a pic. (The pic includes El Paso because there breaks between the data) And funny enough this is the article I got the code from.

Announcements

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors