Average across Date Ranges Criteria is Met

[LindonB]

Hi There. I am trying to return the value over one or multiple date ranges when some criteria is met. In the following, for example, sometimes the value is all I need, but when there is a date range, I need to return the average for the day of the week over the weeks listed. Here, I would need the average of the Mondays in the date ranges of 2017-01-02 to 2017-01-08 and 2017-01-16 to 2017-01-30.

A Generate Rows tools seems to not work, as all dates in the range already exist; for context, this is a artificial dataset, but the goal is to filter out holiday weeks and take the centralized averages instead.

As Always,

Thank you!

[danrh · Accepted solution]

Generate Rows can still work --- in this example, I split off any records that have ranges, Generate Rows to fill in the gaps in the ranges, then Join the original data set to get the values needed for the average.  After that just Union the data back together and take your average with a Summarize:

Could you add some more details regarding the need to "filter out holiday weeks"?  Do you have a pre-defined list of holidays?  Is it the whole week that the holiday lands in that gets filtered out?  An example of what the result might look like would be awesome.

[LindonB]

Thanks so much. That was very helpful.