I have a card for Top 20 Selling Styles that includes Sales, Units Sold, and Quantity On Hand. It is powered by Sales and Inventory datasets that are updated weekly with a date tied to each week.
The person I am building this card for will be utilizing a date filter to change the card to the time period they want to see. For the Quantity On Hand however, I need to only show the latest week of the time period this person selects, since summing up inventory every week doesn't make sense. If filtering to "Previous Quarter," I only want to show them the Quantity On Hand value of the final week within that quarter.
I don't think this could be done within the dataflow since the value will be changing depending on the period selected (correct me if wrong please), so I tried a Beast Mode. I got close, but it isn't exactly what I was needing. First I created a definition of the latest week by building the below:
Max Date: MAX(MAX(Date)) OVER ()
This returns the final date of the filtered period. I then built this to use it:
CASE WHEN Date = Max Date
THEN SUM(Quantity On Hand)
ELSE 0
END
It works, but the issue with this is that the Date column has to be on the card for the formula to be utilized or else it just returns 0, which defeats the purpose of using the date filters to create the report.
