Forum Discussion

Peetkorls's avatar
Peetkorls
New Member
3 years ago

Comparing date series based on different time periods.

I have a Start and End date for Individuals (which is different for some). Each individual is to have a meeting every 12weeks from start date uptill end date. How do I compare each individual meeting date to see if the fall within the range of 12 weeks or not?

I am hoping for ideas on this.

I have created the data interval for every 12 weeks, and i have each meeting date.

 

Using PowerBI Power Query

 

This is a subset of the table for the Meetings.

UID      Contact 1      Contact 2     Contact 3      Contact 4

DA203/04/2020null15/09/202005/01/2021
N0021/04/202005/08/2020

18/11/2020

01/12/2020
N00601/04/202008/07/202005/11/2020null

 

This is the table for start and end dates. 

UID      start date     End Date

DA21/22/20201/22/2022
N001/23/20192/7/2021
N0061/22/20202/4/2022

 

I used this formular to create a list of all the 12 week periods as intervals:

= Table.AddColumn(#"Added Custom2", "Interval", each List.Dates([start date],[Number of 12Week Periods],#duration(84,0,0,0)))

Need Help

4 Replies

  • Peter_Beck's avatar
    Peter_Beck
    Resolver II
    3 years ago

    Hi -

     

    I don't think using an iterval is relevant here. An interval is just a duration, it does not say when the interval starts or ends.

     

    To compare each date to see if it is inside the start date and end date you can just do a comparison like you would with a number, using >= and <= (or > and < if you want to exclude the actual start or end dates).

     

    Hope this helps,

     

    Peter

    • Peetkorls's avatar
      Peetkorls
      New Member
      3 years ago

      Hi Namesake, 

      I appreciate you putting your time into this to help. 

      Allow me to try and add some context to the issue.

      The scope of the problem, involves an individual or student having a review meeting every 12weeks form start date up till end date. So at every point they have a meeting i want to check if that meeting is within the correct 12 week period given the date it happened.

      Lets say they are in program for a year which translates to about 4 meetings, I am trying to see if :

      Meeting 1 held between (Start date) and (Start date +12 weeks) 

      Meeting 2 held between (Start date + 12 weeks) and (Start date + 24 weeks)

      Meeting 2 held between (Start date + 24 weeks) and (Start date + 36 weeks) and so on.

      Given that each student or individuals start date and end date is different adds another level complexity to it.

       

      I think i just gave you a whole chapter... lol. sorry

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Why not just change all of the start dates to the same start date, and therefore all end dates to the same end date? So no matter the actual 12 week date interval start date, you just make it one single 12 week interval.

     

    Know what I mean?

     

    -- Nate

    • Peetkorls's avatar
      Peetkorls
      New Member
      3 years ago

      Hi Nate, Thanks so much for your reply.

      Unfortunately, I cant change the start dates as it is a realfife problem. Its from students actuall start dates for different programs in a university which take admissions at varying points in time.

      I am begining to wonder if its even possible to make such a comparison within pbi.

      Still searching though...

      Appreciate your contribution once again.