Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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")
3) Finally, with a combination of HLookup and Vlookup, I convert the dates into a month/ week numbers. Existing products are ignored
I would really appreciate if you could help me with this.
Thanks
Solved! Go to 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:
Here is a demo, please try it:
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.
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:
Year =
VALUE ( RIGHT ( Table[Date], 2 ) ) + 2000
or if you have date column
Year =
YEAR ( Table[Date] )
SalesPrevYears =
CALCULATE (
SUM ( Table[Sales] ),
FILTER (
ALL ( Table ),
Table[Product] = EARLIER ( Table[Product] )
&& Table[Year] < EARLIER ( Table[Year] )
)
)
Product Type =
IF ( ISBLANK ( Table[SalesPrevYears ] ), "New Product", "Existing" )
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?
Proud to be a Super User!
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
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:
Here is a demo, please try it:
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.
Thanks a lot, it worked perfect!!
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 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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |