How to dynamically return the latest value for a filtered date range

Mickey
Mickey Member
in Beast Mode

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.

Tagged:

Best Answer

  • ggenovese
    ggenovese Coach
    edited June 19 Answer ✓

    That's right, you mentioned that it was a top 20 report at the top. I believe that you can still get it to work with the following changes:

    1. Include the Style in your fixed functions: SUM(SUM(Sales) FIXED (BY Style))
    2. Sort on Date Descending, then Sales FIXED
    3. Limit rows to 20

Answers

  • ggenovese
    ggenovese Coach
    edited June 19

    Hi @Mickey - If you come at this from the completely opposite direction you can get to a solution that works. Here are the steps:

    1. Create FIXED functions for Sales and Units Sold. Example: SUM(SUM(`Sales`) FIXED ())
    2. Place "Date", "Sales FIXED", "Units Sold FIXED", and "Quantity On Hand" in your table card.
    3. You should have the sum of Sales and the sum of Units Sold repeated on each row at this point, as well as each discrete Date and Quantity On Hand value
    4. Make sure to graph by Day
    5. Sort by Date Descending
    6. Limit Rows to 1
    7. In General, go to the Hide Columns property and enter 1 to hide the Date column
  • Mickey
    Mickey Member

    @ggenovese Limiting the rows to 1 removes the purpose of the card since I'm listing the Top 20 Styles by Sales during the period the client filters to, so I'll need to sort by Sales. It also only returns the sum value the Quantity On Hand of whatever the Style ID at the top of all rows is, not the sum of the final week of On Hand of the filtered period

  • ggenovese
    ggenovese Coach
    edited June 19 Answer ✓

    That's right, you mentioned that it was a top 20 report at the top. I believe that you can still get it to work with the following changes:

    1. Include the Style in your fixed functions: SUM(SUM(Sales) FIXED (BY Style))
    2. Sort on Date Descending, then Sales FIXED
    3. Limit rows to 20
  • Mickey
    Mickey Member

    @ggenovese Okay that worked. Thank you so much for your help!

Categories