Sales Curve 2 weeks prior to event

Hi Aaron,

You can add a filter on [Seat Sold Flag] = Y to eliminate those 1/1/1900 results for unsold seats.

To get the -14 day to include all prior sales, use a case statement such as this...

RSUM (
  CASE WHEN MAX( [Order Days Prior to Performance] ) = -14 THEN ( [Total Ticket Count] , [Order Days Prior to Performance <= -14] )
     WHEN MAX( [Order Days Prior to Performance] ) = 0 THEN ( [Total Ticket Count] , [Order Days Prior to Performance >= 0] )
     ELSE [Total Ticket Count] END
)

In the results for Day = -14 this overrides the Order Days filter and includes all days before or on 14 days Prior to Performance. Just in case there are any orders after the Performance Date, the results for Day = 0 include the Order Days 0 or later. Otherwise it just includes the ticket count for the given day prior to performance.

I added this formula in but the X axis is now showing from the first sale leading up to the performance. I am hoping to only show the final 2 weeks leading up to a performance. Order days up to the performance on the X axis also didn't work. Here is what the graph looks like.

Aaron Rivkin, do you have the field [Order Days Prior to Performance]? It looks like I'm doing the same type of dashboard tile that you are and I'm not getting your same issue. But, I'm using a filter field that is slightly different, and maybe you and I are not using the same cube. Here is what I have: 

Mark Levine Is the ticket count starting at 0 for 14 days before? When I do 2 weeks to the performance date, the ticket count starts at 0 instead of including the tickets purchased prior to 2 weeks before the event. Chris Wallingford Thoughts?

Oh, I see the issue. My report is showing sales per-day and you're looking for sales-as-of. I should have realized you wanted to see a SUM (as that's what I wanted too), I gave up with a sad face because I don't think there is a way to get it to work. Sorry

I had the same misunderstanding as Mark. Apologies for that. We can update the formula with ALL([Days in Order Date]) for the two end points of the curve. This will override the Order Date grouping on the X-Axis for those two points.

RSUM (
  CASE WHEN MAX( [Order Days Prior to Performance] ) = -14 THEN ( [Total Ticket Count] , [Order Days Prior to Performance <= -14] , ALL([Days in Order Date]) )
     WHEN MAX( [Order Days Prior to Performance] ) = 0 THEN ( [Total Ticket Count] , [Order Days Prior to Performance >= 0] , ALL([Days in Order Date]) )
     ELSE [Total Ticket Count] END
)

I had the same misunderstanding as Mark. Apologies for that. We can update the formula with ALL([Days in Order Date]) for the two end points of the curve. This will override the Order Date grouping on the X-Axis for those two points.

RSUM (
  CASE WHEN MAX( [Order Days Prior to Performance] ) = -14 THEN ( [Total Ticket Count] , [Order Days Prior to Performance <= -14] , ALL([Days in Order Date]) )
     WHEN MAX( [Order Days Prior to Performance] ) = 0 THEN ( [Total Ticket Count] , [Order Days Prior to Performance >= 0] , ALL([Days in Order Date]) )
     ELSE [Total Ticket Count] END
)