Equivalent of DATESBETWEEN(LASTDATE... ?

[DerekK]

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 --

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?

 

[Matt Allington]

Datesbetween is an inbuilt time intelligence function. To use it, you must
1. Have a calendar table. https://exceleratorbi.com.au/power-pivot-calendar-tables/
2. The first parameter must be the date column in the calendar table.

It is unlikely this function will help you. Instead you will probably need a custom time intelligence function. https://exceleratorbi.com.au/dax-time-intelligence-beginners/

I wouldn’t even guess the formula without seeing the table structure and relationships.

 

[DerekK]

Thank you, Matt.

I've got a client table:

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

and a contract table:

ContractID	ClientID	Contract Name	Contract Amount	Start Date	End Date
1	1	Client A contract 2018	£1,000.00	1-May-2018	30-Apr-2019
2	2	Client B contract 2018	£2,000.00	1-Aug-2018	31-Jul-2019
3	1	Client A contract 2019	£3,000.00	1-May-2019	30-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 --

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!