Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I've searched quite extensively and I believe it is down to a syntax error but I need held with the DATESBETWEEN DAX.
I have a Column return a count (i.e. "1") if the DATESBETWEEN are from a lookupvalue X (the release date) to lookup y (release date + X time). I have having difficulty with the latter part. There are two tables 1) Table 1 and 2) Summary. See below -
Solved! Go to Solution.
no reason to be afraid of variables, in this case they are just there to make your code more readable. You can write this code without the variables, just replace the variables with the expressions in the variables.
When it comes to dates not recognized as dates, despite being formated, I have come across a couple these situations lately. A workaround is to create a new column like this newDate=date(year(oldDate);month(oldDate);day(oldDate)), and replace oldDate with newDate in your DAX-code
Hi, @Paul911
you could try writing your formula like this:
Column After 3 Mo. =
VAR _startDate =
LOOKUPVALUE ( Summary[Rel. Date], Summary[Program], Table1[ProductFamilyGroup] )
VAR _numberOfMonths = 3
VAR _endDate =
DATE ( YEAR ( _startDate ), MONTH ( _startDate ) + _numberOfMonths, DAY ( _startDate ) )
RETURN
CALCULATE (
COUNT ( Table1[DefectID] ),
DATESBETWEEN ( Table1[DateReported], _startDate, _endDate )
)
Since you have not added any sample data in your post, I have not checked if this works.
Cheers,
Sturla
If this post helps, then please consider Accepting it as the solution. Kudos are nice too.
Hi @sturlaws
Thanks for the prompt feedback. Was hoping to avoid creating VARs but intuitively, that makes sense.
I did encounter an argument where function 'DATE' has the wrong data type. Both [Rel. Date] and [Date Reported] are formatted as type Date. Any pointers here without assessing the sample data? I'll also check the forums.
Regards,
Paul
no reason to be afraid of variables, in this case they are just there to make your code more readable. You can write this code without the variables, just replace the variables with the expressions in the variables.
When it comes to dates not recognized as dates, despite being formated, I have come across a couple these situations lately. A workaround is to create a new column like this newDate=date(year(oldDate);month(oldDate);day(oldDate)), and replace oldDate with newDate in your DAX-code
@sturlaws That works. So does wrapping the overall formula with IFERROR. Is this due to leap year? In any case, thanks for your help.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |