Equivalent of DATESBETWEEN(LASTDATE... ?

DerekK

Board Regular
Joined
Jun 18, 2007
Messages
84
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:

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Matt Allington

MrExcel MVP
Joined
Dec 18, 2014
Messages
1,222

DerekK

Board Regular
Joined
Jun 18, 2007
Messages
84
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,102,541
Messages
5,487,462
Members
407,601
Latest member
soccer4ward

This Week's Hot Topics

Top