If date falls within a date range in a list, then apply the end date of the date from the list

jkinnick
Contributor

If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

I have the following:

• Module with a column for Lease Ending Date - My particular date I'm using for this example is 7/14/19

• List built that houses bi-weekly ending dates the Lease Ending Date can fall into (Goes from 2/3/19 to 2/17/19 to 3/3/19, etc.) - The date range the Lease Ending Date falls into is 7/8/19 - 7/21/19. (Image of List attached)

 

In a new column, I want a formula that will look at the Lease Ending Date, determine which date range the Lease Ending Date falls into within the list and apply the end date, 7/21/19 in this case, as the result

 

Can someone please help me build out a formula for this?

 

Thanks,

3 REPLIES 3
PaulRitner
Valued Contributor

Re: If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

 

The easiest way I've accomplished tasks like this it to create a child list (to your Bi-Weekly End Dates) and list out the children dates that should be included... then to a lookup to fetch the parent.

 

For example

List 1  item :  3/3/19

    Child list 2 items as children of 3/3/19

           2/18/19

           2/19/19

           2/20/19

           2/21/19

           2/22/19

           2/23/19

           2/24/19

           2/25/19

           2/26/19

           2/27/19

           2/28/19

           3/1/19

           3/2/19

           3/3/19

     

Do the FINDITEM on  list 2, then lookup the parent using the FINDITEM result.

 

Cheers,

 

Paul

 

 

 

VIGNESH.M
Certified Master Anaplanner

Re: If date falls within a date range in a list, then apply the end date of the date from the list

Hi,

Try this solution,

Step 1: Add Start Date and End date of the week as shown below

1.png

 

Lease Date Mod with Country Dimension and line item - Lease Date and Biweekly Date

2.png

 

Step 2: Create a module with Country, Biweekly Date List as dimensions and line item - Date.

3.png

Line item, Date is Biweekly List Format and Summary is first non blank and apply the logic as shown.

 

Logic: IF Lease Date Mod.Lease Date >= Biweekly End Date.Week Start Date AND Lease Date Mod.Lease Date <= Biweekly End Date.Week End Date THEN ITEM(Biweekly End Date) ELSE BLANK

 

Step 3: Refer this Date line item in Lease Date module

4.png

 

Thanks,

Vignesh M

 

 

ChrisHeathcote
Community Boss

Re: If date falls within a date range in a list, then apply the end date of the date from the list

@jkinnick 

 

What you are faced with here is a mapping problem - how can Anaplan map the 'Lease ending date,' to the 'Bi-weekly ending dates.'

 

Mappings can be established either within list properties or a module. However, as the mapping is from a one to many we can not use list properties as we will not be able to add a range of dates to each 'Bi-weekly ending date,' in a list property. 

 

Therefore, I would recommend building a new module dimensioned by the Anaplan 'Time' dimension and containing one line item which has been formatted as the list, 'Bi-weekly ending date,' and name this line item as per the list name, 'Bi-weekly ending date.'

 

Then for each date in the time dimension allocate it a, 'Bi-weekly ending date,' list item. 

 

I would recommend that you build this in excel and import via a csv as this may be time consuming.

 

Now you are ready to build the formula which will reference the mapping module and pull the relevant Bi-weekly ending date. As long as your target module contains a time dimension all you need to do is reference the 'Bi-weekly ending date,' in the source ( the mapping module ) to pull through the relevant bi-weekly ending date.

 

Even if you add additional dimensions to your target this should no impact on how the b-weekly ending date pulls through to your target. Just ensure that the line item in your target is formatted to DATE.

 

Hope this helps,

 

Chris

 

 

Chris Heathcote
Bedford Consulting

Gold Partner and Regional Partner of the Year 2021, EMEA