Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to create a distribution that looks at the number of product downloads inside of a promotion, across time.
The duration of each Promotion is not always equivalent. For example the Min Duration (Days) = 30, Max Duration (Days) = 900, Mean Duration = 210 Days . Because each duration is different, I would like to convert the dates into DayNumbers within the time period (For example, if a promotion ran for 30 days, the first date would become 1, the next 2, etc. until 30 days are represented)
Is it possible to create the dates between the StartDate and Endate of a Promotion to then convert into DayNumbers and create the percentage of time?
Example Data
Promotion | StartDate | EndDate |
Promotion 1 | 1/1/2019 | 3/1/2019 |
Promotion 2 | 5/1/2019 | 6/1/2019 |
Desired Outcome
Promotion | Date | DayNumber | %Duration |
Promotion 1 | 1/1/2019 | 1 | 2% |
Promotion 1 | 1/2/2019 | 2 | 3% |
Promotion 1 | 1/3/2019 | 3 | 5% |
Promotion 1 | 1/4/2019 | 4 | 7% |
Promotion 1 | 1/5/2019 | 5 | 8% |
Promotion 1 | 1/6/2019 | 6 | 10% |
Promotion 1 | 1/7/2019 | 7 | 12% |
Promotion 1 | 1/8/2019 | 8 | 13% |
Promotion 1 | 1/9/2019 | 9 | 15% |
Promotion 1 | 1/10/2019 | 10 | 17% |
Promotion 1 | 1/11/2019 | 11 | 18% |
Promotion 1 | 1/12/2019 | 12 | 20% |
Promotion 1 | 1/13/2019 | 13 | 22% |
Promotion 1 | 1/14/2019 | 14 | 23% |
Promotion 1 | 1/15/2019 | 15 | 25% |
Promotion 1 | 1/16/2019 | 16 | 27% |
Promotion 1 | 1/17/2019 | 17 | 28% |
Promotion 1 | 1/18/2019 | 18 | 30% |
Promotion 1 | 1/19/2019 | 19 | 32% |
Promotion 1 | 1/20/2019 | 20 | 33% |
Promotion 1 | 1/21/2019 | 21 | 35% |
Promotion 1 | 1/22/2019 | 22 | 37% |
Promotion 1 | 1/23/2019 | 23 | 38% |
Promotion 1 | 1/24/2019 | 24 | 40% |
Promotion 1 | 1/25/2019 | 25 | 42% |
Promotion 1 | 1/26/2019 | 26 | 43% |
Promotion 1 | 1/27/2019 | 27 | 45% |
Promotion 1 | 1/28/2019 | 28 | 47% |
Promotion 1 | 1/29/2019 | 29 | 48% |
Promotion 1 | 1/30/2019 | 30 | 50% |
Promotion 1 | 1/31/2019 | 31 | 52% |
Promotion 1 | 2/1/2019 | 32 | 53% |
Promotion 1 | 2/2/2019 | 33 | 55% |
Promotion 1 | 2/3/2019 | 34 | 57% |
Promotion 1 | 2/4/2019 | 35 | 58% |
Promotion 1 | 2/5/2019 | 36 | 60% |
Promotion 1 | 2/6/2019 | 37 | 62% |
Promotion 1 | 2/7/2019 | 38 | 63% |
Promotion 1 | 2/8/2019 | 39 | 65% |
Promotion 1 | 2/9/2019 | 40 | 67% |
Promotion 1 | 2/10/2019 | 41 | 68% |
Promotion 1 | 2/11/2019 | 42 | 70% |
Promotion 1 | 2/12/2019 | 43 | 72% |
Promotion 1 | 2/13/2019 | 44 | 73% |
Promotion 1 | 2/14/2019 | 45 | 75% |
Promotion 1 | 2/15/2019 | 46 | 77% |
Promotion 1 | 2/16/2019 | 47 | 78% |
Promotion 1 | 2/17/2019 | 48 | 80% |
Promotion 1 | 2/18/2019 | 49 | 82% |
Promotion 1 | 2/19/2019 | 50 | 83% |
Promotion 1 | 2/20/2019 | 51 | 85% |
Promotion 1 | 2/21/2019 | 52 | 87% |
Promotion 1 | 2/22/2019 | 53 | 88% |
Promotion 1 | 2/23/2019 | 54 | 90% |
Promotion 1 | 2/24/2019 | 55 | 92% |
Promotion 1 | 2/25/2019 | 56 | 93% |
Promotion 1 | 2/26/2019 | 57 | 95% |
Promotion 1 | 2/27/2019 | 58 | 97% |
Promotion 1 | 2/28/2019 | 59 | 98% |
Promotion 1 | 3/1/2019 | 60 | 100% |
Promotion 2 | 5/1/2019 | 1 | 3% |
Promotion 2 | 5/2/2019 | 2 | 6% |
Promotion 2 | 5/3/2019 | 3 | 9% |
Promotion 2 | 5/4/2019 | 4 | 13% |
Promotion 2 | 5/5/2019 | 5 | 16% |
Promotion 2 | 5/6/2019 | 6 | 19% |
Promotion 2 | 5/7/2019 | 7 | 22% |
Promotion 2 | 5/8/2019 | 8 | 25% |
Promotion 2 | 5/9/2019 | 9 | 28% |
Promotion 2 | 5/10/2019 | 10 | 31% |
Promotion 2 | 5/11/2019 | 11 | 34% |
Promotion 2 | 5/12/2019 | 12 | 38% |
Promotion 2 | 5/13/2019 | 13 | 41% |
Promotion 2 | 5/14/2019 | 14 | 44% |
Promotion 2 | 5/15/2019 | 15 | 47% |
Promotion 2 | 5/16/2019 | 16 | 50% |
Promotion 2 | 5/17/2019 | 17 | 53% |
Promotion 2 | 5/18/2019 | 18 | 56% |
Promotion 2 | 5/19/2019 | 19 | 59% |
Promotion 2 | 5/20/2019 | 20 | 63% |
Promotion 2 | 5/21/2019 | 21 | 66% |
Promotion 2 | 5/22/2019 | 22 | 69% |
Promotion 2 | 5/23/2019 | 23 | 72% |
Promotion 2 | 5/24/2019 | 24 | 75% |
Promotion 2 | 5/25/2019 | 25 | 78% |
Promotion 2 | 5/26/2019 | 26 | 81% |
Promotion 2 | 5/27/2019 | 27 | 84% |
Promotion 2 | 5/28/2019 | 28 | 88% |
Promotion 2 | 5/29/2019 | 29 | 91% |
Promotion 2 | 5/30/2019 | 30 | 94% |
Promotion 2 | 5/31/2019 | 31 | 97% |
Promotion 2 | 6/1/2019 | 32 | 100% |
Solved! Go to Solution.
Get a date table first.
Then create a table with this
Pdates = SELECTCOLUMNS ( GENERATE ( 'Promotion', DATESBETWEEN ( 'Dates'[Date], Promotion[StartDate], Promotion[EndDate] ) ), "Promotion", Promotion[Promotion], "Date", 'Dates'[Date] )
Add a column with this
DayNumber = VAR _CurrentPromotion = Pdates[Promotion] RETURN RANKX ( FILTER ( PDates, Pdates[Promotion] = _CurrentPromotion ), Pdates[Date], , ASC, Dense )
and for now, you'll have to work out the percentage yourself.
Hi,
You may download my PBI file from here.
Hope this helps.
Power Query to number the days example:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCijKz80vyczPUzBU0lEy1DfUNzIwtAQyjWHMWB1kVUZAKVOEKjO4qlgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Promotion = _t, StartDate = _t, EndDate = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Promotion", type text}, {"StartDate", type date}, {"EndDate", type date}}), #"Added Custom" = Table.AddColumn(#"Changed Type", "Date", each List.Transform( { Number.From ( [StartDate] ) ..Number.From ( [EndDate] ) }, each Date.From (_) )), #"Expanded Date" = Table.ExpandListColumn(#"Added Custom", "Date"), #"Removed Columns" = Table.RemoveColumns(#"Expanded Date",{"StartDate", "EndDate"}), #"Grouped Rows" = Table.Group(#"Removed Columns", {"Promotion"}, {{"Dates", each _, type table [Promotion=text, Date=date]}}), #"Added Custom1" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.AddIndexColumn([Dates],"Day",1,1)), #"Expanded Custom" = Table.ExpandTableColumn(#"Added Custom1", "Custom", {"Promotion", "Date", "Day"}, {"Promotion.1", "Date", "Day"}), #"Removed Columns1" = Table.RemoveColumns(#"Expanded Custom",{"Promotion", "Dates"}) in #"Removed Columns1"
Proud to be a Super User!
Get a date table first.
Then create a table with this
Pdates = SELECTCOLUMNS ( GENERATE ( 'Promotion', DATESBETWEEN ( 'Dates'[Date], Promotion[StartDate], Promotion[EndDate] ) ), "Promotion", Promotion[Promotion], "Date", 'Dates'[Date] )
Add a column with this
DayNumber = VAR _CurrentPromotion = Pdates[Promotion] RETURN RANKX ( FILTER ( PDates, Pdates[Promotion] = _CurrentPromotion ), Pdates[Date], , ASC, Dense )
and for now, you'll have to work out the percentage yourself.
Holy Smokes that is beautiful. Thanks very much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |