cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
JacktheYeti Frequent Visitor
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

PromotionStartDateEndDate
Promotion 11/1/20193/1/2019
Promotion 25/1/2019

6/1/2019

 

Desired Outcome

PromotionDateDayNumber%Duration
Promotion 11/1/201912%
Promotion 11/2/201923%
Promotion 11/3/201935%
Promotion 11/4/201947%
Promotion 11/5/201958%
Promotion 11/6/2019610%
Promotion 11/7/2019712%
Promotion 11/8/2019813%
Promotion 11/9/2019915%
Promotion 11/10/20191017%
Promotion 11/11/20191118%
Promotion 11/12/20191220%
Promotion 11/13/20191322%
Promotion 11/14/20191423%
Promotion 11/15/20191525%
Promotion 11/16/20191627%
Promotion 11/17/20191728%
Promotion 11/18/20191830%
Promotion 11/19/20191932%
Promotion 11/20/20192033%
Promotion 11/21/20192135%
Promotion 11/22/20192237%
Promotion 11/23/20192338%
Promotion 11/24/20192440%
Promotion 11/25/20192542%
Promotion 11/26/20192643%
Promotion 11/27/20192745%
Promotion 11/28/20192847%
Promotion 11/29/20192948%
Promotion 11/30/20193050%
Promotion 11/31/20193152%
Promotion 12/1/20193253%
Promotion 12/2/20193355%
Promotion 12/3/20193457%
Promotion 12/4/20193558%
Promotion 12/5/20193660%
Promotion 12/6/20193762%
Promotion 12/7/20193863%
Promotion 12/8/20193965%
Promotion 12/9/20194067%
Promotion 12/10/20194168%
Promotion 12/11/20194270%
Promotion 12/12/20194372%
Promotion 12/13/20194473%
Promotion 12/14/20194575%
Promotion 12/15/20194677%
Promotion 12/16/20194778%
Promotion 12/17/20194880%
Promotion 12/18/20194982%
Promotion 12/19/20195083%
Promotion 12/20/20195185%
Promotion 12/21/20195287%
Promotion 12/22/20195388%
Promotion 12/23/20195490%
Promotion 12/24/20195592%
Promotion 12/25/20195693%
Promotion 12/26/20195795%
Promotion 12/27/20195897%
Promotion 12/28/20195998%
Promotion 13/1/201960100%
Promotion 25/1/201913%
Promotion 25/2/201926%
Promotion 25/3/201939%
Promotion 25/4/2019413%
Promotion 25/5/2019516%
Promotion 25/6/2019619%
Promotion 25/7/2019722%
Promotion 25/8/2019825%
Promotion 25/9/2019928%
Promotion 25/10/20191031%
Promotion 25/11/20191134%
Promotion 25/12/20191238%
Promotion 25/13/20191341%
Promotion 25/14/20191444%
Promotion 25/15/20191547%
Promotion 25/16/20191650%
Promotion 25/17/20191753%
Promotion 25/18/20191856%
Promotion 25/19/20191959%
Promotion 25/20/20192063%
Promotion 25/21/20192166%
Promotion 25/22/20192269%
Promotion 25/23/20192372%
Promotion 25/24/20192475%
Promotion 25/25/20192578%
Promotion 25/26/20192681%
Promotion 25/27/20192784%
Promotion 25/28/20192888%
Promotion 25/29/20192991%
Promotion 25/30/20193094%
Promotion 25/31/20193197%
Promotion 26/1/201932100%
1 ACCEPTED SOLUTION

Accepted Solutions
HotChilli New Contributor
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]
)

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 4
HotChilli New Contributor
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]
)

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.

ChrisMendoza Senior Member
Senior Member

Re: Calculate DayNumber between Two Dates

@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"
JacktheYeti Frequent Visitor
Frequent Visitor

Re: Calculate DayNumber between Two Dates

Holy Smokes that is beautiful. Thanks very much!

Super User
Super User

Re: Calculate DayNumber between Two Dates

Hi,

You may download my PBI file from here.

Hope this helps.

Helpful resources

Announcements
Virtual Launch Event

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

Power BI Helps Homeless and Trouble Youth

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

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 379 members 3,569 guests
Please welcome our newest community members: