Best Of

I believe there is a glitch right now with the filter tiles. The way I have gotten around it is to use the formula function in the filter and then wrap the date function around each item so that it sees them both as dates. Like this:

DATE(`mydatefield`) < DATE('6/30/2021')

@ozarkram - I think that @GrantSmith 's approach is a better option here. He has included safeguards for any null values. If you choose @MichelleH's solution you will get a null value if any of the three fields are null.

You can do some simple date manipulation to get the Monday of the current week with a Formula Tile

`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY

DAYOFWEEK returns a number between 1 (Sunday) and 7 (Saturday).

The CASE statement is checking if it's a Sunday to subtract 6 days instead of 1.

-2 is to reduce the days to subtract to get to Monday. For example Tuesday (3) we need to subtract only 1 day instead of 3 days.

You can then tie it all together with a CONCAT function and a DATE_FORMAT:

CONCAT('Week of ', DATE_FORMAT(`Date` - INTERVAL (CASE WHEN DAYOFWEEK(`Date`) = 1 THEN 6 ELSE (DAYOFWEEK(`Date`) - 2) END) DAY, '%m/%d'))

@NathanDorsch , if your first filter is removing all data prior to 6/30/22, then you might not have any data greater than 6/30/21 available after that first filter is applied.

Whenever you want to do an aggregation you need to put your function on the outside of your case when statement. If you do case when 'x' then max(date), the beast mode just does the calculation for each individual line, so it will just take the max date of each row in your dataset. If you instead do MAX(case when 'x' then 'date') then the beast mode will treat that as an aggregation and will look for the MAX date in your table groupings.