DATESBETWEEN Date and Time Field

[WEW2]

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, 

[perassolli]

Ward, i'm with the same issue.

[MattAllington]

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]

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]

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

[aksh]

Hi

Sorry, I didn't get your message.

I want to know if I am creating a new measure (not a new column) can't I use a date column that has multiple duplicate values?

What is the resolution to this problem?

[Sean]

Time Intelligence Functions which DATEDD is one of - require a Date/Calendar table in your Data Model.

Go to Matt's link here and create one

http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/

Then change your formula

PY Amount = CALCULATE(SUM([TaxAmt]), DATEADD(CalendarTableName[DateColumn], -1, YEAR) )