Calculate DayNumber between Two Dates

[JacktheYeti]

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%

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[ChrisMendoza]

@JacktheYeti -

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"

[HotChilli · Accepted 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.

[JacktheYeti]

Holy Smokes that is beautiful. Thanks very much!