cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
hurtinalbertan Frequent Visitor
Frequent Visitor

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

1 ACCEPTED SOLUTION

Accepted Solutions
hurtinalbertan Frequent Visitor
Frequent Visitor

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

5 REPLIES 5
Moderator Eric_Zhang
Moderator

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.

hurtinalbertan Frequent Visitor
Frequent Visitor

Re: Invalid OADATE Value

Can you send me contact information?

Moderator Eric_Zhang
Moderator

Re: Invalid OADATE Value

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

hurtinalbertan Frequent Visitor
Frequent Visitor

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

PBIDAX Frequent Visitor
Frequent Visitor

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.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 65 members 1,361 guests
Please welcome our newest community members: