Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
mahawkins3
Helper I
Helper I

Issue loading data with "date" data type

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

4 REPLIES 4
Shashanth
Frequent Visitor

Hey @Maha ,

 

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

 

Regards

GilbertQ
Super User
Super User

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!







Power BI Blog

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!

 

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors