cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Bbrown44 Member
Member

How to create a new conditional column (or new measure) using two date variable for range

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?

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Microsoft v-lionel-msft
Microsoft

Re: How to create a new conditional column (or new measure) using two date variable for range

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

 

 bb4.PNG

2. Extract dates that are not in the range.

 

Table 3 = 
SELECTCOLUMNS(
    FILTER(
        'Table 2',
        [Conditional column] = "NO"
    ),
    "New Date",
    [Date]
)

 

bb5.PNG

 

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.

View solution in original post

2 REPLIES 2
Super User IV
Super User IV

Re: How to create a new conditional column (or new measure) using two date variable for range

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.





Did I answer your question? Mark my post as a solution!
In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin


Microsoft v-lionel-msft
Microsoft

Re: How to create a new conditional column (or new measure) using two date variable for range

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

 

 bb4.PNG

2. Extract dates that are not in the range.

 

Table 3 = 
SELECTCOLUMNS(
    FILTER(
        'Table 2',
        [Conditional column] = "NO"
    ),
    "New Date",
    [Date]
)

 

bb5.PNG

 

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.

View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors