Dates - building a Date Table from date column with multiple entries per day - help required

[Anonymous]

I am trying to create a Date Table which I can relate to my model "Archive" Table.

I have a problem creating my Date column because I am trying to build it from the Date column in the Archive Table. The Date column in the Archive table is comprised of MULTIPLE ENTRIES PER DATE. My datais comprised of 3 hourly average values per day. that means there are 8 recorded day/times for any given day for all of my columns of data being collected.

I am failing in my attempt to create a Date Table based on the Dates in the Archive Table.

I have tried the following DAX expression to return a Date column

"Dates = CALENDAR(FIRSTDATE(Archive[Date]), LASTDATE(Archive[Date]))"

The error returned is "A date column containing duplicate dates was specified in the call to function 'FIRSTDATE'. This is not supported."

I wonder if there is another DAX expression or trick I could use to try and build the Date column for the Date Table.

I want the Date Table to only have dates that contain actual data, not a generic Date column which spans a "general" time expanse eg 1 jan 2018 to 31 dec 2019. I want specific dates as related to actual dates ie first date is 27 jul 2018 to last date in the Archive Table.

I don't think CalendarAuto is what I want.

Can anyone suggest anything I could try?

[CheenuSing]

Hi @Anonymous ,

Can you share some data screen shot of Archve table.  What is the data type of Archive(Date]. 

Have you tride MIN and MAX instead of FIRSTDATE and LASTDATE in the CAlendar function.

Cheers

CheeuSing

[Anonymous]

Hi Cheenusing

My date column in Archive Table is set to Date/Time.

if you see the Screenshot and look at Date 22/10/2019, you will see 8 consecutive 22/10/2019 entries and multiple entries for every other day also.

I did try Min and Max and I got the same error - "a table of multiple values was supplied where a single value was expected".

I also tried Minx and Maxx  - but think maybe my expression was not really correct. I got a different error - "too few arguments were passed to the MINX function. The minimu argument count for the function is 2".

regards

Donna

Zoom view Date column entriesView of Archive Table

[CheenuSing]

Hi @Anonymous ,

I tried simulating your date column

My values are

01/07/2017 12:00:00 AM

01/07/2017 03:00:00 PM

06/07/2017 09:00:00 AM

06/07/2017 12:00:00 PM

06/07/2017 03:00:00 PM

And used MIN and Max  it worked without any problem

When using FIRSTDATE and LASTDATE i encountered the same error as you.

Alternatively you can create another colum from the current Date column as DateValues which has only the Date and no time.

Use this column to generate the date table.

Cheers

CheenuSing

[Anonymous · Accepted solution]

Hi again CheenuSing

thanks again for your continued help.

Oddly I tried

"CALENDAR(MINX(Archive,[Date]), MAXX(Archive,[Date]))"      and it worked this time. I did this in a new table.

I tried the same expression in the other Date Table I was trying to make and the same expression failed with same error "a table of multiple values was supplied where a single value was expected"

I am not sure why the expression worked in one table but not another. it makes me suspicious/suspect something is not right.

it makes me think is the Error coming because I am trying to make this in a Date Table which already has some date functions in it? CalendarAuto and NowDate.

I have just tried something completely random and built new table and put in same expression - it worked. so maybe trying to use this expression if there is an existing date column in the table - will create the error. does that seem logical?

anyway - I think with your help I have arrived at the correct solution. my expression

date = CALENDAR(MINX(Archive,[Date]), MAXX(Archive,[Date])

works for me and gives the info I need.

thank you for your time CheenuSing. In answer to your other suggestion about converting to date/time to date only, I am not sure how that would affect the data in the model. I suppose I wanted to preserve the original time stamps, maybe they will come in for analysis later on.

thanks again. I think problem is resolved. 🙂

Donna

[CheenuSing]

Hi @Anonymous ,

Glad to note you found a solution.

Cheers

CheenuSing