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

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

5 Replies

  • Eric_Zhang's avatar
    Eric_Zhang
    Microsoft Employee

    Anonymous

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

    • Anonymous's avatar
      Anonymous
      Not applicable

      Can you send me contact information?

      • Eric_Zhang's avatar
        Eric_Zhang
        Microsoft Employee

        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.