Skip to main content
cancel
Showing results for 
Search instead 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

Reply
MickyG
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.

 

MickyG_0-1631901968431.png

 

MickyG_1-1631902001615.png

 

1 ACCEPTED SOLUTION
v-xiaotang
Community Support
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))

vxiaotang_0-1632216105837.png

 

 

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.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
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))

vxiaotang_0-1632216105837.png

 

 

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.

vanessafvg
Super User
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!




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.

 

MickyG_0-1631907536681.png

 

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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