Pivot Table Filtering

[blueice2627]

Hi All,

I'm looking for a way (if possible) to allow my filters in a pivot table be based off of an "if" statement or something similar. The data that i'm pulling from is a little complicated to write out... but here goes nothing.

I work at a hotel and every night there is a snap shot of what we have booked for the future. Because it is the future only, it doesn't show were yesterday finished so the numbers are accurate for the future and that is it. I have another table that puts all my past dates together with the numbers that the day finished with. I have a column called "snap date" to show what day the data was "snapped" from.

To put some numbers out there to demonstrate:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Snap[/TD]
[TD]Date[/TD]
[TD]Rooms
[/TD]
[/TR]
[TR]
[TD]04/10/19
[/TD]
[TD]04/10/19
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]04/10/19
[/TD]
[TD]04/11/19
[/TD]
[TD]10
[/TD]
[/TR]
[TR]
[TD]04/10/19
[/TD]
[TD]04/12/19
[/TD]
[TD]11
[/TD]
[/TR]
[TR]
[TD]04/10/19
[/TD]
[TD]04/13/19
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]04/11/19
[/TD]
[TD]04/11/19
[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]04/11/19
[/TD]
[TD]04/12/19
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]04/11/19
[/TD]
[TD]04/13/19
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]04/12/19
[/TD]
[TD]04/12/19
[/TD]
[TD]13
[/TD]
[/TR]
[TR]
[TD]04/12/19
[/TD]
[TD]04/13/19
[/TD]
[TD]8
[/TD]
[/TR]
[TR]
[TD]04/12/19
[/TD]
[TD]04/14/19
[/TD]
[TD]7
[/TD]
[/TR]
</tbody>[/TABLE]

So here is what i'm trying to do. I want to see the state of the hotel at any given "snap" date. If i put the snap date to 04/11/19, i will get a return of 04/11 with 10 rooms, 04/12 with 12 rooms and 04/13 with 5, where if i put 04/12 in the snap, I'll only get 04/12 at 13 roooms 04/13 at 8 Rooms and 04/14 with 7 rooms.

The problem with that is, if i want to know my totals month to date, the only way to get yesterdays final number is to go to yesterdays number filtered to that days snap date. Essentially, i want to be able to select a snap date of 04/12 and receive 04/10 with 8 rooms, 04/11 with 10 rooms along with 04/12 with 13 rooms, 04/13 with 8 rooms and 04/14 with 7 rooms.

The only thing i can think of doing at this point is having it so that when i import the data for the report with the days final numbers, have it update the "snap date" of all the past data to "today" but i can be a data purist at times so i prefer to not mess with the original data unless i absolutely have to.

Hope that makes sense! Let me know if i need to clarify more.

Thanks,
Craig

 

[Worf]

Do you still need help with this?