Comparing date series based on different time periods.

[Peetkorls]

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

DA2	03/04/2020	null	15/09/2020	05/01/2021
N00	21/04/2020	05/08/2020	18/11/2020	01/12/2020
N006	01/04/2020	08/07/2020	05/11/2020	null

This is the table for start and end dates. 

UID      start date     End Date

DA2	1/22/2020	1/22/2022
N00	1/23/2019	2/7/2021
N006	1/22/2020	2/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)))

[Anonymous]

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]

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.

[Peter_Beck]

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]

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