Reply
Frequent Visitor
Posts: 6
Registered: ‎12-22-2016
Accepted Solution

Invalid OADATE Value

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:

InvalidOADateValue.jpg

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

1154018025

MDSFleetAttributes -

FleetUnitNumber BusinessUnitCode Capacity EffectiveDate TerminationDate

1154022425180252016-08-012016-09-01
1154022425180252016-09-012016-10-01
1154022425180252016-10-012016-11-01
1154022425180252016-11-012016-12-01
1154022425180252016-12-012017-01-31
1154022425180252017-01-312017-02-01
1154020031180252017-02-012017-03-13
1154020031180252017-03-132099-12-31

 

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


Accepted Solutions
Frequent Visitor
Posts: 6
Registered: ‎12-22-2016

Re: Invalid OADATE Value

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

View solution in original post


All Replies
Moderator
Posts: 3,051
Registered: ‎03-06-2016

Re: Invalid OADATE Value

@hurtinalbertan

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

Frequent Visitor
Posts: 6
Registered: ‎12-22-2016

Re: Invalid OADATE Value

Can you send me contact information?

Moderator
Posts: 3,051
Registered: ‎03-06-2016

Re: Invalid OADATE Value

[ Edited ]

@hurtinalbertan

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.

Frequent Visitor
Posts: 6
Registered: ‎12-22-2016

Re: Invalid OADATE Value

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

Frequent Visitor
Posts: 2
Registered: ‎12-06-2018

Re: Invalid OADATE Value

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.