FIRSTDATE Query

[gmazza76]

Good morning,

I am trying to pull through the first date from a column in another table which has the Date as "dd/mm/yy hh:mm:ss" which is in the format I need, but I am getting an error saying "A date column contains duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported" I take it is due to the format with the time in as well. As the date would appear multiple times in my column just the rime would make this unique.

I am using the formula below

=CALCULATE(FIRSTDATE(Source[Start Date]),
    FILTER(Source,Source[LOB]=Outcome[LOB]),
    FILTER(Source,Source[Brand]=Outcome[Brand]))

Is there a way I can pull this data through with the criteria in Power Pivot?

thanks in advance
Gavin

 

[jorismoerings]

Hi,

The way you've structured the filter opens up the possibility that multiple dates come back. FILTER(Source,Source[LOB]=Outcome[LOB]) can be true or FILTER(Source,Source[Brand]=Outcome[Brand])) can be true or both can be true) hence 1 date with 3 options. The addition of time in the field has no bearing on the working of FIRSTDATE.

As the date would appear multiple times in my column just the rime would make this unique.

As you mentioned unique , i assume both conditions must be true. If that's a correct assumption, changes your DAX formula to:

=CALCULATE(FIRSTDATE(Source[Start Date]),
    FILTER(Source, Source[LOB]=Outcome[LOB] && Source[Brand]=Outcome[Brand]))

 

[gmazza76]

thanks for this @jorismoerings, but I seem to get the error.
"Calculation error in column 'Outcome'[]: A date column contains duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported."

And I haven't a clue what this means. Hopefully you will

 

[jorismoerings]

Hi,

This means there are multiple values while there's only expected. That would mean the filter logic doesn't return a unique values while it should to make sure FIRSTDATE works accurately.
Two things left to suggest:

1. make sure the filter logic is accurate and only returns the data from which you need the First date from;
2. make sure there's no data inconsistency in the source table.

 

[gmazza76]

I think I have found the issue.
It seems like the date/time format is in "dd/mm/yy hh:mm:ss" which would normally be fine, but is there anyway I can change the "ss" to "SSS" as the time rounds up, where as if I could run this into 100th of a second as my data holds it might be easier

 

[jorismoerings]

Hi,

Don't think so. The Column with dates needs to be unique. So if your column contains values on same date but with different timestamps (eg: 8/24/2021 15:30:00 and 8/24/2021 17:02:00) it will return "the multiple values" error. If you actually want the first date with the first timestamp. Better to use the MIN function instead of FIRSTDATE.