Forum Discussion
EWBWEBB
Helper III
Helper IIIAverage over dates for benchmark
Hi there I am trying to understand the difference in sales pre and post event to see if there is any change. I have payment files for 6 dates, in a single table 'SaleFiles' With columns: ...
lbendlin
Super User
Super UserPlease provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
EWBWEBBHelper III
Helper IIIHi lbendlin
I can't seem to attach a file. Hope the below will suffice.
Date,DateKey
| 07/05/2022 | 44688 |
| 04/06/2022 | 44716 |
| 02/07/2022 | 44744 |
| 30/07/2022 | 44772 |
| 27/08/2022 | 44800 |
| 24/09/2022 | 44828 |
SalesValue,SaleName,MembershipDate,DateKey,CustomerID
| 668.6190094 | Membership | 07/05/2022 | 44688 | DM10135 |
| 334.2732227 | Membership | 04/06/2022 | 44716 | DM10135 |
| 686.3950474 | Membership | 02/07/2022 | 44744 | DM10135 |
| 338.7135908 | Membership | 30/07/2022 | 44772 | DM10135 |
| 676.3266122 | Membership | 27/08/2022 | 44800 | DM10135 |
| 30.53928729 | Ticket | 24/09/2022 | 44828 | DM10135 |
| 162.5897358 | Membership | 24/09/2022 | 44828 | DM10135 |
| 41.17208482 | Ticket | 04/06/2022 | 44716 | DM34851 |
| -791.208116 | Refund | 02/07/2022 | 44744 | DM34851 |
| 61.18740263 | Ticket | 02/07/2022 | 44744 | DM34851 |
| -384.412413 | Refund | 30/07/2022 | 44772 | DM34851 |
| 479.2099203 | Membership | 30/07/2022 | 44772 | DM34851 |
| -720.964709 | Refund | 27/08/2022 | 44800 | DM34851 |
| 86.16605538 | Ticket | 27/08/2022 | 44800 | DM34851 |
| 100.9107386 | Refund | 24/09/2022 | 44828 | DM34851 |
| 66.284255 | Ticket | 24/09/2022 | 44828 | DM34851 |
| -374.723553 | Refund | 02/07/2022 | 44744 | DM34853 |
| 88.10315328 | Ticket | 02/07/2022 | 44744 | DM34853 |
| 290.156434 | Membership | 30/07/2022 | 44772 | DM34853 |
| -194.973433 | Refund | 27/08/2022 | 44800 | DM34853 |
| 15.38424038 | Ticket | 27/08/2022 | 44800 | DM34853 |
| 175.180816 | Membership | 27/08/2022 | 44800 | DM34853 |
| 198.816163 | Membership | 07/05/2022 | 44688 | DM34861 |
| -434.096583 | Refund | 02/07/2022 | 44744 | DM34861 |
| -223.012008 | Refund | 27/08/2022 | 44800 | DM34861 |
| 907.0228001 | Ticket | 07/05/2022 | 44688 | DM34871 |
| 333.9358515 | Ticket | 04/06/2022 | 44716 | DM34871 |
| 564.2276465 | Ticket | 02/07/2022 | 44744 | DM34871 |
| 908.2565283 | Ticket | 30/07/2022 | 44772 | DM34871 |
| 335.7169272 | Ticket | 27/08/2022 | 44800 | DM34871 |
| 566.0098346 | Membership | 27/08/2022 | 44800 | DM34871 |
| 799.0188638 | Ticket | 24/09/2022 | 44828 | DM34871 |
Expected Result
I would want to be able to filter all this by another table DimCustomer. Which contains the following
| CustID | Location | Type | AgeGroup | SpendLimit |
| DM10135 | UK | Gold | 25-30 | £25,000 |