Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Convert dates into weeks after appearance

Hi All,

I created a table in xls that converts dates into week number after their appearance (I use months in the screen below so reduce number of columns/ rows). I used this table to track new products and their performance. 

Can anyone help me to do something similar in Power BI? The steps I follow in xls are:

1) I start with raw data like the table below.

 

Product

Date

Sales

Product A

Dec-18

10508284.87

Product A

Jan-19

5096901.747

Product A

Feb-19

5123430.92

Product A

Mar-19

7438428.002

Product A

Apr-19

6081655.041

Product A

May-19

5484704.181

Product A

Jun-19

6590211.959

Product A

Jul-19

4976915.122

Product B

Mar-19

444.87491

Product B

Apr-19

123.63507

Product B

May-19

522.01474

Product B

Jun-19

1551.25028

Product B

Jul-19

144.76927

Product C

Jun-19

139126.4386

Product C

Jul-19

56573.3036

 

 

 

2) Using a Pivot table, I convert the raw data into a table and insert a column (column O)  to identify New products (using a formula similar to = if(e3>0,"Existing","New Product")

Pivot table xls.png

 

3) Finally, with a combination of HLookup and Vlookup, I convert the dates into a month/ week numbers. Existing products are ignored

 

Formulas in xls.png

 

I would really appreciate if you could help me with this.

 

Thanks

1 ACCEPTED SOLUTION

Hi @Anonymous ,

you can try the following M Query to convert the formula, the "Source" is in my query and starting with "Changed Type", is in yours:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZC9ioJBDEVfZflqDUkmySSlumwhCPZi4V+3qIgWvv3OrI2fpLvF4d6TbDbD+nY5Pg73r9kwGcin36dDD6jo7AJeh+3kA4rpcnduQTEskKBKCv2c9h0iLlIQgjNmtbu1UKW4sANiCs2uHTJ0MlVAobzp2dfEpaIAeQotH93bNJCJIDRy6LcFiWpBCsRjp/m7uEj/kARlyEu7nQ9WFGve8i/NDEhSJUNeyqRKwIrsOdONqck0Zx4vLUY1JYgN2rsth3qPmtYCBUtjtn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"First Sale Column", each List.Min([Date]), type date}, {"ALL", each _, type table [Product=text, Date=date, Sales=number]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Date", "Sales"}, {"Date", "Sales"})
in
    #"Expanded ALL"

Results are as follows:

11.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Eb6lmb9aTN9CjND_tcRvhAsBXdj7bGQeGnlQUf4oRLqh1g?e=RzRDk3

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
danextian
Super User
Super User

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?






Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu.
Anonymous
Not applicable

Hi mate,

 

Thanks for your help, but I don't think I was clear with what I am after. 

Basically what I want is that the first month that registers sales become Month 1, the second becomes Month 2 and so on.

I was able to find the date of the first sale using the formula below, and then I counted the days from the first sale column to the month and divide it by 30, so that gives me the month number after launch. 

As I will be bringing weekly data and I have many products, is there a way to convert the formula below into a query, so I only import the product I need.

 

Really appreciate your help.

Thanks 

 

First Sale Column = CALCULATE ( FIRSTNONBLANK('Table1'[Date],1), ALLEXCEPT('Table1', 'Table1'[Product]) )

Hi @Anonymous ,

you can try the following M Query to convert the formula, the "Source" is in my query and starting with "Changed Type", is in yours:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bZC9ioJBDEVfZflqDUkmySSlumwhCPZi4V+3qIgWvv3OrI2fpLvF4d6TbDbD+nY5Pg73r9kwGcin36dDD6jo7AJeh+3kA4rpcnduQTEskKBKCv2c9h0iLlIQgjNmtbu1UKW4sANiCs2uHTJ0MlVAobzp2dfEpaIAeQotH93bNJCJIDRy6LcFiWpBCsRjp/m7uEj/kARlyEu7nQ9WFGve8i/NDEhSJUNeyqRKwIrsOdONqck0Zx4vLUY1JYgN2rsth3qPmtYCBUtjtn8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Product = _t, Date = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Product", type text}, {"Date", type date}, {"Sales", type number}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Product"}, {{"First Sale Column", each List.Min([Date]), type date}, {"ALL", each _, type table [Product=text, Date=date, Sales=number]}}),
    #"Expanded ALL" = Table.ExpandTableColumn(#"Grouped Rows", "ALL", {"Date", "Sales"}, {"Date", "Sales"})
in
    #"Expanded ALL"

Results are as follows:

11.PNG

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/Eb6lmb9aTN9CjND_tcRvhAsBXdj7bGQeGnlQUf4oRLqh1g?e=RzRDk3

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Thanks a lot, it worked perfect!!

Anonymous
Not applicable

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.

Anonymous
Not applicable

Hi Ross,

 

Thanks for your help, but I don't think I was clear with what I am after. 

Basically what I want is that the first month that registers sales become Month 1, the second becomes Month 2 and so on.

I was able to find the date of the first sale using the formula below, and then I counted the days from the first sale column to the month and divide it by 30, so that gives me the month number after launch. 

As I will be bringing weekly data and I have many products, is there a way to convert the formula below into a query, so I only import the product I need.

 

Really appreciate your help.

Thanks 

 

First Sale Column = CALCULATE ( FIRSTNONBLANK('Table1'[Date],1), ALLEXCEPT('Table1', 'Table1'[Product]) )

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.