Convert dates into weeks after appearance

The solution would be to create a Date Table, which contains a Week column.  You then join your other data tables to this date table and can make use of the Week information as you need to.

For example, you join your product table to the date table.  In your visual, you bring in the Week column from the date table and the product information from your product table.

This Power Query function can create a date table  (I call it a query of name 'fnDateTable'):

//Create Date Dimension
let
    Source = (StartDate as date, EndDate as date)=>
let
    //Capture the date range from the parameters
    StartDate = #date(Date.Year(StartDate), Date.Month(StartDate), 
    Date.Day(StartDate)),
    EndDate = #date(Date.Year(EndDate), Date.Month(EndDate), 
    Date.Day(EndDate)),
//Get the number of dates that will be required for the table
    GetDateCount = Duration.Days(EndDate - StartDate) + 1,
//Take the count of dates and turn it into a list of dates
    GetDateList = List.Dates(StartDate, GetDateCount, 
    #duration(1,0,0,0)),
//Convert the list into a table
    DateListToTable = Table.FromList(GetDateList, 
    Splitter.SplitByNothing(), {"Date"}, null, ExtraValues.Error),
//Create various date attributes from the date column
    //Add Year Column
    YearNumber = Table.AddColumn(DateListToTable, "Year", 
    each Date.Year([Date])),
//Add Quarter Column
    QuarterNumber = Table.AddColumn(YearNumber, "Quarter", 
    each "Q" & Number.ToText(Date.QuarterOfYear([Date]))),
//Add Day of Week Column
    DayOfWeek = Table.AddColumn(QuarterNumber, "Day of Week", 
    each Date.ToText([Date],"dddd")),
//Add Week Number Column
    WeekNumber= Table.AddColumn(DayOfWeek, "Week Number", 
    each (if [Day of Week] = "Sunday" then -1 else 0) + Date.WeekOfYear([Date])),
//Add Month Number Column
    MonthNumber = Table.AddColumn(WeekNumber, "Month Number", 
    each Date.Month([Date])),
//Add Month Name Column
    MonthName = Table.AddColumn(MonthNumber , "Month", 
    each Date.ToText([Date],"MMMM"))
in
    MonthName
in
    Source

You can then create the date table with another query calling it like:

let
    Source = fnDateTable(Date.From("2019-01-01"), Date.From("2019-12-31")),
    #"Set Field Types" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Year", Int64.Type}, {"Quarter", type text}, {"Week Number", Int64.Type}, {"Month Number", Int64.Type}, {"Month", type text}, {"Day of Week", type text}})
in
    #"Set Field Types"

This is just an example of how you might use it, you could use a function to set the state/end dates of the date range. You could do other things in the fnDateTable if you need extra date data, you can remove items as well that you don't need.

Hi Anonymous ,

By "after their appearance" do you mean that if a product had sales in the previous year/s it is an existing product? If so, try this:

• Create a year column if you don't have one yet. Try:

Year =
VALUE ( RIGHT ( Table[Date], 2 ) ) + 2000​

or if you have date column

Year = 
YEAR ( Table[Date] )​

• Create another column to compute for sales from the previous years:

SalesPrevYears =
CALCULATE (
    SUM ( Table[Sales] ),
    FILTER (
        ALL ( Table ),
        Table[Product] = EARLIER ( Table[Product] )
            && Table[Year] < EARLIER ( Table[Year] )
    )
)​

• A column for Product Type

Product Type =
IF ( ISBLANK ( Table[SalesPrevYears ] ), "New Product", "Existing" )
​

• Measure to ignore sales from Existing Products =

Sales of New Products =
CALCULATE ( SUM ( Table[Sales] ), Table[Product Type] = "New Product" )​

Now for your month Number, when does the month number start? How do you determine which one is month 1, 2, 3, etc?