Forum Discussion
Last year with 2 different dates
Hi all,
I hope you can help me with a problem within I'm struggling for the last 2 months.
My scenario:
I have 1 fact table with 2 dates.
Let's say that one date is order date and the other date is ship date. That means that I can have 5 order dates in January that will be shipped in the future, for example in February, march and June.
What I need is to be able to filter by both dates and see all the time the measure compared with last year.
I need Number by Order date. Number by Ship date, Number by Order date last year and Number by Ship date last year where Number is the number of distinctcount of products.
Dummy data:
| Order date | Ship date | Products |
| 02/01/2019 | 02/02/2019 | 1 |
| 03/01/2019 | 02/02/2019 | 2 |
| 03/01/2019 | 03/01/2019 | 1 |
| 02/01/2019 | 02/03/2019 | 3 |
| 02/01/2019 | 04/03/2019 | 4 |
| 04/01/2019 | 04/04/2019 | 5 |
| 02/01/2019 | 02/04/2019 | 6 |
| 02/01/2019 | 31/12/2019 | 7 |
| 02/01/2019 | 31/12/2019 | 8 |
| 04/01/2019 | 10/06/2019 | 9 |
| 04/01/2019 | 07/01/2019 | 10 |
| 02/01/2019 | 03/01/2019 | 11 |
| 02/01/2019 | 03/03/2019 | 12 |
| 03/01/2019 | 02/04/2019 | 13 |
| 03/01/2019 | 07/03/2019 | 14 |
| 03/01/2019 | 02/05/2019 | 15 |
| 03/01/2019 | 02/06/2019 | 16 |
| 04/01/2019 | 05/06/2019 | 17 |
| 04/01/2019 | 05/06/2019 | 18 |
| 05/01/2019 | 02/03/2019 | 19 |
| 04/01/2018 | 04/04/2018 | 20 |
| 02/01/2018 | 02/05/2018 | 21 |
| 03/01/2018 | 03/06/2018 | 22 |
| 04/01/2018 | 10/01/2018 | 23 |
| 04/01/2018 | 04/04/2018 | 24 |
| 02/01/2018 | 02/05/2018 | 25 |
| 04/01/2018 | 04/07/2018 | 26 |
| 02/01/2018 | 02/08/2018 | 27 |
| 05/01/2018 | 05/09/2018 | 28 |
| 03/01/2018 | 03/10/2018 | 29 |
| 02/01/2018 | 02/11/2018 | 30 |
| 04/01/2018 | 04/05/2018 | 31 |
| 01/01/2018 | 01/05/2018 | 32 |
| 01/01/2018 | 01/06/2018 | 33 |
| 01/01/2018 | 01/06/2018 | 34 |
| 05/01/2018 | 05/06/2018 | 35 |
| 06/01/2018 | 31/16/2018 | 36 |
| 01/01/2018 | 01/02/2018 | 37 |
| 01/01/2018 | 01/02/2018 | 38 |
| 06/01/2018 | 10/02/2018 | 39 |
| 04/01/2018 | 04/03/2018 | 40 |
| 04/01/2018 | 04/04/2018 | 41 |
| 06/01/2018 | 06/04/2018 | 42 |
| 03/01/2018 | 03/04/2018 | 43 |
| 03/01/2018 | 07/05/2018 | 44 |
Hope you can help me ! Thank you in advance !!
2 Replies
Hi luxpbi ,
>>What I need is to be able to filter by both dates and see all the time the measure compared with last year.
I am not sure what desired result would you want, could you please share your sample data or desired output screenshots for further analysis? You can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Amy
