Forum Discussion

WEW2's avatar
WEW2
Regular Visitor
10 years ago

DATESBETWEEN Date and Time Field

Im confused.  I've read for Date and time functions that's its best to have a date or calendar table.  I have a table called reservations with the following fields:  DriverID (string), PickUpDate (DateTime), Revenue(Double)

 

THe second table is called shifts with the fields DriverID, ShiftStart (Datetime),ShiftEnd(DateTime).  In the second table I want to add a colum to calculate the total revenue per shift by driver.  The problem is that many shifts run after midnight so the time of day has to part of the calculation.  I've tried: CALCULATE(Sum(reservations[Revenue]),Filter(reservations,[DriverID]=shifts([DriverID]),DATESBETWEEN(reservations[PickUPDate],shift[shiftstart],shift[shiftend])).

 

I get "A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'. This not supported."

 

I use a similar function with just a date (no time) and it works fine.  What am I missing here as I'm not how a calendar or date table helps with time?

 

Thanks, 

 

 

Tutorial Requests

6 Replies

  • MattAllington's avatar
    MattAllington
    Icon for Community Champion rankCommunity Champion
    10 years ago

    DAX is a very powerful language.  It has an ability to morph in ways you wouldn't normally expect based on other software.  If you create a new column, subtract the end column from the start column, then format this column as a decimal number, you will get a fraction of a full day worked.

     

    Another example.  If you have 2 columns of integers Table[Int1] and Table[Int2], the following formula will work

     

    Table[Int1] & Table[Int2]*2

    DAX will multiply column 2 by 2, then append the result via concatenation to the back of column 1.  Not normally you would normally expect, but very powerful.

     

    Now another thing is that I would not be using a calculated column for your example.  It is very common for Excel users to do this, but is is 'normally' not the best way (normally based on my experience working with Excel users migrating to Power Pivot).  You should consider using a Measure for your calcuation.  

     

     

    • aksh's avatar
      aksh
      Icon for Microsoft Employee rankMicrosoft Employee
      10 years ago

      Hi

       

      I created the below new measure

       

      PreviousYearAmount = CALCULATE(SUM[TaxAmt], DATEADD([Invoice Date],-1,YEAR))

       

      And I get the below error

      Calculation error in measure: A Date column with duplicate dates specified in the call function 'DATEADD'. This is not supported.

       

      How do I resolve this?

      Many invoices can be generated on a single date.

       

      So in the filters provided, if year=2015 and month= Dec , my visual should comparison of Dec-15 amount with Dec-14.

      if year=2014 and month= Dec then visual should comparison of Dec-13 and Dec-14.

       

       

      • MattAllington's avatar
        MattAllington
        Icon for Community Champion rankCommunity Champion
        10 years ago

        The first parameter of DATEADD should be the column in your Calendar table.  It looks like you are using a measure or a column in your fact table