Forum Discussion

mahawkins3's avatar
mahawkins3
Helper I
9 years ago

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 dif...
Need Help
GilbertQ's avatar
GilbertQ
Super User
9 years ago

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's avatar
mahawkins3
Helper I
9 years ago

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's avatar
    mahawkins3
    Helper I
    9 years ago

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