Equivalent of DATESBETWEEN(LASTDATE... ?
Results 1 to 3 of 3

Thread: Equivalent of DATESBETWEEN(LASTDATE... ?
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2007
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Equivalent of DATESBETWEEN(LASTDATE... ?

    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 by DerekK; Jul 7th, 2019 at 07:40 AM.

  2. #2
    MrExcel MVP
    Join Date
    Dec 2014
    Location
    Sydney, Australia
    Posts
    1,174
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Equivalent of DATESBETWEEN(LASTDATE... ?

    Datesbetween is an inbuilt time intelligence function. To use it, you must
    1. Have a calendar table. https://exceleratorbi.com.au/power-p...lendar-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-tim...nce-beginners/

    I wouldn’t even guess the formula without seeing the table structure and relationships.
    Matt Allington is a professional Power BI Consultant, Power BI Trainer and Microsoft MVP based in Sydney Australia. Matt is author of the Power BI book Supercharge Power BI and the Power Pivot Book Supercharge Excel.
    What is Power BI?

  3. #3
    Board Regular
    Join Date
    Jun 2007
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Equivalent of DATESBETWEEN(LASTDATE... ?

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

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •