Datediff many to many relationships

[romovaro]

Hello

I want to calculate the diff between 2 dates in diff tables with relationship many to many

Both files are the same.

One column is called CUID and is a unique customer number

and GLD is the Go live date of the project.

I'm trying this formula but I get an error: A table of multiple values was supplied where a single value was expected.

I can have blanks if date is not yet confirmed

GLD Diff = DATEDIFF(

    'Planview Daily Extract'[GLD],

    LOOKUPVALUE('Baseline-PV Daily Extract'[GLDate], 'Baseline-PV Daily Extract'[CUID],'Planview Daily Extract'[CUID]),

    MONTH

)

Any idea?

thanks

@jgeddes 

[Muhammad110 · Accepted solution]

The error message you're receiving indicates that the lookup value function is returning multiple values where a single value was expected. This can happen if there are multiple records in the 'Baseline-PV Daily Extract' table that match the CUID in the 'Planview Daily Extract' table.

To avoid this error, you can use the maxx function to return the maximum value of the 'GLDate' column for each CUID in the 'Baseline-PV Daily Extract' table. This ensures that only a single value is returned for each CUID.

Here's the updated formula:

GLD Diff = DATEDIFF(
'Planview Daily Extract'[GLD],
MAXX(FILTER('Baseline-PV Daily Extract', 'Baseline-PV Daily Extract'[CUID] = EARLIER('Planview Daily Extract'[CUID])), 'Baseline-PV Daily Extract'[GLDate]),
MONTH
)

hope this helps 

[romovaro]

Thanks for the help. It works.