Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

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: Capacity CALCULATION = var Start_Date...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
9 years ago

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's avatar
PBIDAX
Frequent Visitor
7 years ago

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.