Forum Discussion
Solved
Calculate DayNumber between Two Dates
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% |
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.
4 Replies
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!
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"
