Prevent end of week data from splitting between to months

[J_Zayak]

Greetings all, I'm scratching my head trying to figure out how to essentially "roll forward" data into the next month if the end of week falls in that month.

 The rows in the 'Sales' contain the $ amount and the date (1/3/2022 etc). The calendar table has an EOW column and the relationship is between the 'Sales' date and calendar date. 

Using the example above, I would want the EOW data for 1/2/2022 to fall in January, not split between the two months. 2/6/2022 would fall into Febuary only, etc.

example tables:

SALES

DateKey	Sales
12/27/2021	500
12/28/2021	600
12/31/2021	1000
1/1/2022	1500
1/2/2022	700

CALENDAR

Date	Month	Year	EOW
12/27/2021	12	2021	1/2/2022
12/28/2021	12	2021	1/2/2022
12/29/2021	12	2021	1/2/2022
12/30/2021	12	2021	1/2/2022
12/31/2021	12	2021	1/2/2022
1/1/2022	12	2022	1/2/2022
1/2/2022	12	2022	1/2/2022

Thanks in advance, hope this makes sense and I've provided enough data.

[CNENFRNL]

Simple enough, just keep the "Month" symbol of a whole week the same as the start of the week,

let
    Source = Table.FromList(List.Dates(#date(2021, 12, 27), 10, #duration(1, 0, 0, 0)), Splitter.SplitByNothing(), {"Date"}),
    EoW = Table.AddColumn(Source, "EoW", each Date.EndOfWeek([Date],Day.Monday)),
    
    #"Virtual Month" = Table.AddColumn(EoW, "Virtual Month", each Date.ToText(Date.StartOfWeek([Date]), "MMM yyyy"))

in
    #"Virtual Month"

[amitchandak · Accepted solution]

@J_Zayak , For That either display data week wise of create week month on week start date or week end date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)

Month Year = format([Week Start Date],"MMM-YYYY")

or

Month Year = format([Week END Date],"MMM-YYYY")

Month Year Sort= format([Week Start Date],"YYYYMM")

or

Month Year Sort= format([Week END Date],"YYYYMM")

[J_Zayak]

This is the way. Thank you