Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
How do I determine using DAX whether the dates between 2 columns (column A and B) are within dates shown in the created calender with an If statement of yes or no for a calculated column?
Are there any solutions to this problem please?
Solved! Go to Solution.
@Anonymous , New columns
New column for Col 1=
var _min = Min(calendar[Date])
var _max = Max(calendar[Date])
return
if([Col1] >= _min && [Col1]<=_max , "Col 1 is in calendar dates", "No")
New column for Col 1=
var _min = Min(calendar[Date])
var _max = Max(calendar[Date])
return
if([Col2] >= _min && [Col2]<=_max , "Col 2 is in calendar dates", "No")
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Hi @Anonymous ,
The created calender in your dataset is a table or a column? I have created a simple sample, please refer to my pbix file to see if it helps you.
Manage the relationship between the two table.
Then create a measure.
Measure =
VAR _mindate =
MINX ( ALL ( 'Table' ), 'Table'[date_1] )
VAR _maxdate =
MAXX ( ALL ( 'Table' ), 'Table'[date_2] )
RETURN
IF (
MAXX ( ALL ( calender ), calender[Date] ) <= _maxdate
&& MAXX ( ALL ( calender ), calender[Date] ) >= _mindate,
"YES",
"NO"
)
If I have misunderstood your meaning, please provide your pbix file without privacy information and desired output.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous , New columns
New column for Col 1=
var _min = Min(calendar[Date])
var _max = Max(calendar[Date])
return
if([Col1] >= _min && [Col1]<=_max , "Col 1 is in calendar dates", "No")
New column for Col 1=
var _min = Min(calendar[Date])
var _max = Max(calendar[Date])
return
if([Col2] >= _min && [Col2]<=_max , "Col 2 is in calendar dates", "No")
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |