Calendar Table - Days from most recent Promo

[tc97]

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!

[amitchandak]

@tc97 , I have done something similar in this file and blog see if these can help

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

[tc97]

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 · Accepted solution]

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.