Equivalent of DATESBETWEEN(LASTDATE... ?

DerekK

Board Regular
Joined
Jun 18, 2007
Messages
90
Office Version
  1. 2003 or older
Platform
  1. Windows
This query pertains to Power Pivot in Excel 2016.

I have a table containing every annual contract, past and present, which includes contract end dates and amounts. (I also have two separate related tables, one for client and one for calendar.)

I'm trying to write a measure for a pivot table that will return the contract amount for those customers whose contract ends within the current fiscal year but who have not yet renewed. (The last, or most recent, contract end date of those who have renewed would then fall outside the current fiscal year.) The measure would look something like this --

Code:
amt2018:=CALCULATE ( 
       SUM ( Contracts[Amount] ) ,
       DATESBETWEEN (
           LASTDATE ( Contracts[Contract End Date] ,
           DATE ( 2019 , 4 , 1 ) ,
           DATE (2020 , 3, 31 )
)

-- but I get the error, "DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument." I've tried to create a "virtual" date column using ADDCOLUMNS and SUMMARIZE but Power Pivot is having none of it.

Can anyone please recommend a workaround for this?
 
Last edited:

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
Upvote 0
Thank you, Matt.

I've got a client table:

ClientIDClient Name
1Client A
2Client B
&c.&c.

and a contract table:

ContractIDClientIDContract NameContract AmountStart DateEnd Date
11Client A contract 2018£1,000.001-May-201830-Apr-2019
22Client B contract 2018£2,000.001-Aug-201831-Jul-2019
31Client A contract 2019£3,000.001-May-201930-Apr-2020
&c.&c.&c.&c.&c.&c.

and a calendar (standard).




The relationships are:

Contracts[ClientID] *=>1 Clients[ClientID]

and

Contracts[End Date] *=>1 Calendar[Date]




The pivot table has:

Rows
Date (Year)
Date (Month)
Clients[Client Name]



In the pivot table, I'm trying write a measure for the Values section which shows all clients whose contracts are coming up for tender, along with the corresponding contract amount. Referring to the above tables, I want to filter out Client A based on the fact that the end date of its most recent contract falls outside the current fiscal year (in other words, we've renewed the 2018 contract).

Looking at the time intelligence article you sent, I tried --

Code:
amt2018:=CALCULATE ( 
       SUM ( Contracts[Amount] ) ,
       FILTER (
          Contracts ,
          MAX ( Contracts[End Date] ) >= DATE ( 2019 , 4 , 1 )
	  &&
	  MAX ( Contracts[End Date] ) <= DATE ( 2020 , 3 , 31 )

)

-- but it didn't work.

Hope you can help!
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top