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.
Happy New Year Everyone!
I have 3 date ranges:
(a) September 5 and 18, 2019,
(b) November 1 and December 31, 2019 and
(c) March 1 and April 30, 2020
I also have a Date variable that I want to evaluate.
I want to create a conditional column that would evaluate if the date variable falls between the 3 date ranges above. If "No" Than "date is outside of date range" would be populated as a new variable.
else if:
I also want to identify which range the date falls within in the new variable (i.e., (a), (b), or (c)) when the variable is within one of the date ranges.
If I could make this happen with a new measure variable even better.
Anyone has any ideas?
Solved! Go to Solution.
Hi @Bbrown44 ,
From your description, I don't quite understand what you want to achieve, I did the following:
1. Create a condintional column
Conditional column =
IF(
( [Date] >= DATE(2019,9,5) && [Date] <= DATE(2019,9,18) )
||
( [Date] >= DATE(2019,11,1) && [Date] <= DATE(2019, 12, 31) )
||
( [Date] >= DATE(2020, 3,1) && [Date] <= DATE(2020, 4, 30) ),
"YES", "NO"
)
2. Extract dates that are not in the range.
Table 3 =
SELECTCOLUMNS(
FILTER(
'Table 2',
[Conditional column] = "NO"
),
"New Date",
[Date]
)
If this is not what you want, could you kindly show what you want in a table?
Especially about these two needs:
N1: " If "No" Than "date is outside of date range" would be populated as a new variable."
N2: "I also want to identify which range the date falls within in the new variable (i.e., (a), (b), or (c)) when the variable is within one of the date ranges."
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Bbrown44 ,
From your description, I don't quite understand what you want to achieve, I did the following:
1. Create a condintional column
Conditional column =
IF(
( [Date] >= DATE(2019,9,5) && [Date] <= DATE(2019,9,18) )
||
( [Date] >= DATE(2019,11,1) && [Date] <= DATE(2019, 12, 31) )
||
( [Date] >= DATE(2020, 3,1) && [Date] <= DATE(2020, 4, 30) ),
"YES", "NO"
)
2. Extract dates that are not in the range.
Table 3 =
SELECTCOLUMNS(
FILTER(
'Table 2',
[Conditional column] = "NO"
),
"New Date",
[Date]
)
If this is not what you want, could you kindly show what you want in a table?
Especially about these two needs:
N1: " If "No" Than "date is outside of date range" would be populated as a new variable."
N2: "I also want to identify which range the date falls within in the new variable (i.e., (a), (b), or (c)) when the variable is within one of the date ranges."
Best regards,
Lionel Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If these date ranges are static. Then put that into If in a column.
If these dynamic, selected from UI. Put them as a filter in calculate.
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |