Forum Discussion
Calendar Table - Days from most recent Promo
Hi,
I'm creating an internal sales dashboard. I've created and joined a calendar table (below), and everything works fine to report on standard date periods (Date, Week, Week day, Month, Year)
| Date | Day Name | Start of Week | Start of Month | Start of Year |
| 01/01/2020 | Wednesday | 30/12/2019 | 01/01/2020 | 01/01/2020 |
| 02/01/2020 | Thursday | 30/12/2019 | 01/01/2020 | 01/01/2020 |
| 03/01/2020 | Friday | 30/12/2019 | 01/01/2020 | 01/01/2020 |
| 04/01/2020 | Saturday | 30/12/2019 | 01/01/2020 | 01/01/2020 |
| 05/01/2020 | Sunday | 30/12/2019 | 01/01/2020 | 01/01/2020 |
| 06/01/2020 | Monday | 06/01/2020 | 01/01/2020 | 01/01/2020 |
| 07/01/2020 | Tuesday | 06/01/2020 | 01/01/2020 | 01/01/2020 |
| 08/01/2020 | Wednesday | 06/01/2020 | 01/01/2020 | 01/01/2020 |
My problem comes when I'm looking at promotion specific data. We run various annual promotions, and the dates for each promo vary each year, so I'd like to add an additional two columns to the calendar table: [Promotion Name] and [Promotion Day]. The promotion information is currently held in another table (example below)
| Promotion Name | Start Date | End Date |
| Promo 1 | 01/01/2020 | 03/01/2020 |
| Promo 2 | 05/01/2020 | 10/01/2020 |
To track promotion performance against a previous cycle, I'd like to join the two tables to lookup the active promotion (if applicable) and note how many days into the promotion we are, leaving blank where no promotion was active.
| Date | Day Name | Start of Week | Start of Month | Start of Year | Promotion Name | Promotion Day |
| 01/01/2020 | Wednesday | 30/12/2019 | 01/01/2020 | 01/01/2020 | Promo 1 | 1 |
| 02/01/2020 | Thursday | 30/12/2019 | 01/01/2020 | 01/01/2020 | Promo 1 | 2 |
| 03/01/2020 | Friday | 30/12/2019 | 01/01/2020 | 01/01/2020 | Promo 1 | 3 |
| 04/01/2020 | Saturday | 30/12/2019 | 01/01/2020 | 01/01/2020 | ||
| 05/01/2020 | Sunday | 30/12/2019 | 01/01/2020 | 01/01/2020 | Promo 2 | 1 |
| 06/01/2020 | Monday | 06/01/2020 | 01/01/2020 | 01/01/2020 | Promo 2 | 2 |
| 07/01/2020 | Tuesday | 06/01/2020 | 01/01/2020 | 01/01/2020 | Promo 2 | 3 |
| 08/01/2020 | Wednesday | 06/01/2020 | 01/01/2020 | 01/01/2020 | Promo 2 | 4 |
Is this possible within PowerBI? Hoping it's quite straightforward and I've missed something obvious...
Thanks in advance!
Hi tc97 ,
You can refer to these blogs to expand date intervals:
Expand the continuous date interval - Microsoft Power BI Community
Expand the continuous date interval (Excluding wee... - Microsoft Power BI Community
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandak
Super User
Hi amitchandak Thanks for your quick response.
Correct me if I'm wrong, but I'm not sure that approach would work for me. I think I need the two additional fields to be within the table itself.
The end result I'm looking for is something similar to the below, but instead of 'Month Name' in each row, I'd like "Promotion Day" (1 = Promo Start Date, 2 = Following Day, and so on)
This will allow us to compare each promotion against previous runs on a daily basis.
Hope that makes sense.
- Icey
Community Support
Hi tc97 ,
You can refer to these blogs to expand date intervals:
Expand the continuous date interval - Microsoft Power BI Community
Expand the continuous date interval (Excluding wee... - Microsoft Power BI Community
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
