I am looking to make Events numbered based on specific dates and make the process dynamic.

[ssm_214]

I have a set of dates for an event. I know that the events usually happen twice a year from certain months. I want to create a column where each event is numbered in order as time goes on, Event 1, Event 2, Event 3, Event 4, etc. I figured i may need a helper column for this but i am not sure where to start.

Any advice is appreciated. Thank you.

I will attach an example file for you to view. Summit Data.xlsx

[ManuelBolz · Accepted solution]

Hello @ssm_214,

I'm not sure if I understand your question correctly. My following code Creates the TimeFrame column based on Approved Date. If the approval date is between December (12) and February (2), enter the number 1, and if the approval date is between May (5) and July (7), enter the number 2.

Did I understand you correctly?

let
    Source = YOUREXCELFILE,
    Type = Table.TransformColumnTypes(Source,{{"Approved Date", type date}}),
    ColumnTimeFrame = Table.AddColumn(Type, "TimeFrame", each 
    if Date.Month([Approved Date]) >= 12 or Date.Month([Approved Date]) <= 2 
        then 1 
    else if Date.Month([Approved Date]) >= 5 or Date.Month([Approved Date]) <= 7 
        then 2
    else null
    , Int64.Type)
in
    ColumnTimeFrame

Best regards from Germany
Manuel Bolz

If this post helped you, please consider Accept as Solution so other members can find it faster.

🤝Follow me on LinkedIn