Issue loading data with "date" data type

[mahawkins3]

Hi,

I'm trying to create a measure that shows the number of new customers in our database for the current day, as well as at the same point the previous day and 7 days ago. Having tried a few different ways of wording the DAX, I always get an error saying "Cannot construct data type date, some of the arguments have values which are not valid.. The exception was raised by the IDataReader interface."

Here's the DAX that I used - apologies for the formatting; I did try pasting it as an image but that didn't work. Basically, "COUNT([ID])" on its own would give the total number of sign-ups, then I've tried to filter it so that it only includes today, yesterday and 7 days ago, and only entries where the CreatedDate is earlier in the day than the current point in time.

SignupsThisTimeToday = CALCULATE(
COUNT([ID]),
DATE(YEAR(Users[CreatedDate]),MONTH(Users[CreatedDate]),DAY(Users[CreatedDate]))=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(TODAY())) ||
DATE(YEAR(Users[CreatedDate]),MONTH(Users[CreatedDate]),DAY(Users[CreatedDate]))=DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),DAY(TODAY()-1)) ||
DATE(YEAR(Users[CreatedDate]),MONTH(Users[CreatedDate]),DAY(Users[CreatedDate]))=DATE(YEAR(TODAY()-7),MONTH(TODAY()-7),DAY(TODAY()-7)),
TIME(HOUR(Users[CreatedDate]),MINUTE(Users[CreatedDate]),SECOND(Users[CreatedDate]))<=TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW()))
)

Any ideas where I'm going wrong?

Thanks in advance,

Matt

[Shashanth]

Hey @Maha ,

from next time please paste the solution you foud, so that we learn from he same.

Regards

[GilbertQ]

Hi @mahawkins3

What I would suggest is rather than trying to do it all at once, rather get it working for TODAY only.

Also is your Users[CreatedDate] column formatted as Date?

You can also use the http://www.daxformatter.com to get your DAX formatted so that it is easier to read.

This is what I would start off with and see if the numbers are correct.

SignupsThisTimeToday =
CALCULATE (
    COUNT ( [ID] ),
    DATE ( YEAR ( Users[CreatedDate] ), MONTH ( Users[CreatedDate] ), DAY ( Users[CreatedDate] ) )
        = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), DAY ( TODAY () ) )
)

[mahawkins3]

Hi @GilbertQ,

[CreatedDate] is formatted as datetime in the source database - I wouldn't have thought that would cause a problem though given it doesn't have any problems using [CreatedDate] as the axis on time-series visuals. I've tried changing the format to date and that doesn't help either.

I tried your DAX, which also didn't work for me, although I can't see any reason why it wouldn't work!

[mahawkins3]

This issue is now resolved. Although the above solution didn't work for me, I found another way around it (using an SQL query to pull the data in the way I wanted it rather than manipulating it with DAX).