Week Starting that is dynamic depending on the date an event starts.

[Brady-Linnebur]

Hello, So I have this currently and it does not quite fit with what I need. As you can see, the Monday Start Calendar correctly ties the whole week to monday's date as shown below. Also the code for the Monday Start Calendar is this, 

Monday Start Calendar = 'Calendar - Weekly'[Date] - WEEKDAY('Calendar - Weekly'[Date], 2) + 1. 

However, I need it to be a little more dynamic. Lets say I have a promotion that starts on a wednesday, instead of the week starting to show a monday start, I need it to show a Wednesday start and the next 6 days after also have the same date as the column above does with a monday start. Or if it starts on a Saturday, I need the week starting to be that Saturday date. Any ideas on a calculation to make this happen? Let me know if I need to explain something further.

[Anonymous · Accepted solution]

Thanks for the reply from @ryan_mayu  and @Shashankgodala , please allow me to provide another insight: 
Hi  @Brady-Linnebur ,

Here are the steps you can follow：

1. Create calculated column.

Step1 =
var _mindate=
MINX(
    FILTER(ALL('Table'),'Table'[Event]=EARLIER('Table'[Event])),[Date])
var _mindateweekday=
WEEKDAY(_mindate,2)
var _test=
IF(
    WEEKDAY('Table'[Date],2)=_mindateweekday,'Table'[Date],BLANK())
RETURN
_test

Step2 =
IF(
    [Step1]=BLANK(),0,1)

Group =
SUMX(
    FILTER(ALL('Table'),
    'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Date]<=EARLIER('Table'[Date])),[Step2])

Step3 =
IF(
    [Step1]=BLANK(),
MINX(
    FILTER(ALL('Table'),
    'Table'[Event]=EARLIER('Table'[Event])&&'Table'[Group]=EARLIER('Table'[Group])&&
    'Table'[Date]<EARLIER('Table'[Date])&&[Step1]<>BLANK()),[Step1]),[Step1])

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Brady-Linnebur]

Step1 =

VAR MinDate =

MINX(

    FILTER(ALL('Crossjoin'), 'Crossjoin'[Campaign Name]=EARLIER('Crossjoin'[Campaign Name])), 'Crossjoin'[Date])

VAR MinDateWeekday =

    WEEKDAY(MinDate, 2)

VAR Test =

    IF(WEEKDAY('Crossjoin'[Date], 2) = MinDateWeekday, 'Crossjoin'[Date], BLANK())

    RETURN

    Test

Above is for the Step1. All this did however, is this,

I would also add I have promotions going on at the same time, not sure if that would change anything. Any ideas on why it didn't do the correct output?

[Shashankgodala]

Hello, 

From the data you have shown, I am assuming all the data is related to one promotion. 

Follow the steps below in your power query:

1. Create a new column that would have a unique week number based on  Year and WeekOfYear.

= Table.AddColumn(#"Changed Type1", "Week of Year", each Number.ToText(Date.Year([Date])) & Number.ToText(Date.WeekOfYear([Date])))

 2. Group the data on Week of Year column and get the earliest date for each week.

= Table.Group(#"Added Custom", {"Week of Year"}, {{"Earliest Date", each List.Min([Date]), type nullable date}})

3. Create a nested join between the resulting tables of the above 2 queries on the Week of Year column and expand the earliest Date column from Grouped Table. 

= Table.NestedJoin(#"Added Custom", {"Week of Year"}, #"Grouped Rows", {"Week of Year"}, "Added Custom", JoinKind.LeftOuter)
= Table.ExpandTableColumn(#"Merged Queries", "Added Custom", {"Earliest Date"}, {"Added Custom.Earliest Date"})

I hope this helps 😊

[Brady-Linnebur]

I will have more than 1 promotion, and sometimes more than 1 promotion happening at one time.

[ryan_mayu]

could you pls provide the expected output based on the sample data you provided?

i think you have more than one promotion? What i think is to display the promotion name instead of weekday name

[Brady-Linnebur]

I do have more than 1 event going on. Below is what I would expect. Based on whatever the first Date of the event is, that would be the week starting.

[ryan_mayu]

how you get the event info? there is something missing in your sample data because we can't do the data transfrom from your sample data to this output.

[Brady-Linnebur]

It is in a promotion table. The table is connected to the week starting table by date.