Invalid OADATE Value

[Anonymous]

Hi All!

I have received this error for the first time...I can't figure out what it is...all of my date fields are the correct type.  Here's the calc:

Capacity CALCULATION =

var Start_Date = min('DIM Calendar'[DateDate])
var End_Date = max('DIM Calendar'[DateDate])

//assuming DimBranch is connected to MDSFleetAttributes if I count the days fleetAttributes, which is filtered only to the branches selected, where I find the truck value located in carrier ref, then I know I can include that truck's capacity in the calc
RETURN

calculate(sumx('MDS FleetAttributes',if('MDS FleetAttributes'[TerminationDate]<End_Date,'MDS FleetAttributes'[TerminationDate],End_Date)-if('MDS FleetAttributes'[EffectiveDate]>=Start_Date,'MDS FleetAttributes'[EffectiveDate],Start_Date)* [Truck Capacity]),
 FILTER ('MDS FleetAttributes','MDS FleetAttributes'[EffectiveDate]<End_Date),('MDS FleetAttributes'[TerminationDate]>=Start_Date))

Calculated column in FleetAttributes:

Truck Capacity = CALCULATE(sum('Capacity Ref'[Capacity]),FILTER('Capacity Ref','Capacity Ref'[FleetUnitNumber]=EARLIER('MDS FleetAttributes'[FleetUnitNumber])))

What I'm trying to do is based on the report min/max date selected, what is the capacity of that truck multiplied by how long it resided at a specific branch within those dates

In the data set below my expected result would be:

If I select 2017-01-01 to 2017-03-01

Branch22425 = 18025*31

Branch 20031 = 18025*29

If I select both of the branches = 18025*60

For the purposed of this measureI I  have 4 tables:

Dim.Calendar

Dim.Branch - bu code

Capacity ref - aggregated table to get capacity average by fleetunitnumber

FleetUnitNumber Capacity

11540

18025

MDSFleetAttributes -

FleetUnitNumber BusinessUnitCode Capacity EffectiveDate TerminationDate

11540	22425	18025	2016-08-01	2016-09-01
11540	22425	18025	2016-09-01	2016-10-01
11540	22425	18025	2016-10-01	2016-11-01
11540	22425	18025	2016-11-01	2016-12-01
11540	22425	18025	2016-12-01	2017-01-31
11540	22425	18025	2017-01-31	2017-02-01
11540	20031	18025	2017-02-01	2017-03-13
11540	20031	18025	2017-03-13	2099-12-31

Hopefully, someone can see what is going on here!  Many thanks!!

[Eric_Zhang]

@Anonymous

Could you share the pbix to help reproduce the error? Once identified, I'll escalate this issue to the product team.

[Anonymous]

Can you send me contact information?

[Eric_Zhang]

@Anonymous

You can upload it to a network drive, such as oneDrive or Dropbox, and send me the link in a private message if you mind your data being public.

[Anonymous · Accepted solution]

Ok, I wasn't able to share but, we found the error.  I needed a DATEDIFF() to convert the dates to numbers so, that solves this issue...but, if I have more than one record that changed over the time the report is sliced , it is adding a day...still troubleshooting that one.

Capacity *Generated Days = VAR Start_Date = MIN('DIM Calendar'[Date])
         VAR End_Date = MAX('DIM Calendar'[Date])
        RETURN
         CALCULATE(SUMX('MDS FleetAttributes',
           ( DATEDIFF(IF('MDS FleetAttributes'[EffectiveDate]>=Start_Date,
               'MDS FleetAttributes'[EffectiveDate],
               Start_Date),
            IF('MDS FleetAttributes'[TerminationDate]<End_Date,
            'MDS FleetAttributes'[TerminationDate],
            End_Date),
            DAY))
          *'MDS FleetAttributes'[Truck Capacity]),
          FILTER('MDS FleetAttributes',
           'MDS FleetAttributes'[EffectiveDate]<End_Date),
           ('MDS FleetAttributes'[TerminationDate]>=Start_Date))

[PBIDAX]

This error message can also result from mismatch in Types. For example, if you're sorting Type Text with Type Decimal. The error message should be a lot clearer.