Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
Hey @Maha ,
from next time please paste the solution you foud, so that we learn from he same.
Regards
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 () ) ) )
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).
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
72 | |
62 | |
51 | |
48 |
User | Count |
---|---|
206 | |
90 | |
62 | |
59 | |
57 |