cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## 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%
1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: Calculate DayNumber between Two Dates

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]
)```

```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 4
New Contributor

## Re: Calculate DayNumber between Two Dates

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]
)```

```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.

Senior Member

## Re: Calculate DayNumber between Two Dates

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]}}),
#"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"```
Frequent Visitor

## Re: Calculate DayNumber between Two Dates

Holy Smokes that is beautiful. Thanks very much!

Super User

## Re: Calculate DayNumber between Two Dates

Hi,

Hope this helps.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 379 members 3,569 guests
Recent signins: