Including missing dates for rolling 7 day average

[SDT]

I have a data set with a complete calendar (includes every date). I also have a table for orders and there will not be an order on every date. Each orders has several dates associated with it (order date, scheduled date, ship date) so there is a link table between the order table and the date table.

I have a line chart with the x-axis being the date from the complete calendar. The measure has set analysis to only include orders and only use the order date from the link table.

I want to show a 7day average (including all calendar days) and used RANGEAVG(ABOVE(expr,0,7)). The problem is not all dates are included because there are some days with zero orders based on the set analysis.

Is there a way to accomplish a true 7 day rolling average?

[Kushal_Chawda · Accepted solution]

@SDT  try below expression

RANGEAVG(ABOVE(expr + sum({1}0),0,7)). 

[SDT · Accepted solution]

@Kushal_Chawda Thank you very much!