Dynamic aggregation based on user selected date grain

david_cunningham

I have a dataset that appends daily to build out a historical view. I want to be able to always show the latest record per date grain based upon the user selected date range and grain.

For example, if a user selected YTD by month, then I would want to show the following values from the last day of each month

SUM(values) from Jan 31st snapshot (ignore all other snapshots in Jan).

SUM(values) from Feb 28th snapshot (ignore all other snapshots in Feb).

SUM(values) from March 31st snapshot (ignore all other snapshots - in Mar), etc.

If a user selected current YTD by Week, I would want to show the following values from the last available snapshot of each week.

SUM(values) from the last day of week 1 (ignore all other snapshots in week1)

SUM(values) from the last day of week 2 (ignore all other snapshots in week2, etc.

I want to avoid doing this in ETL if possible, because I want it to be as responsive as possible and work across a variety of user selected date ranges/grains.

I've been playing around with window/fixed functions, but can't seem to get them to work. Details below

GrantSmith

Beastmodes don't have access to the selected date grain on the date selector so you won't be able to do it that way. You could utilize a variable to select the date grain which you would have access to.