Max Case Statement
I would like to create a beastmode that will calculate the case fill percentage for the most current fiscal week I have in my data.
For example, if I have fiscal weeks 1-45 in my data, I would like the calculate the case fill percentage for week 45 only. Each week I will receive new data, so next week when I get week 46, I would like the card to automatically calculate the case fill % for week 46 rather then having to manually change the fiscal week by using filter in the card. I cannot use the date range filter to pull 'current week' or even 'previous week' because we receive new data on Thursday's so when we roll over to the new week the date filter will be incorrect.
I thought the below beastmode would work, but it does not seem to pull any data
`Fiscal Calendar Week` = MAX(`Fiscal Calendar Week`)
(SUM(`Original Order Cases`)- SUM(`Cut`)) / SUM(`Original Order Cases`)