How to find if date is between two ranges

[Anonymous]

Hi,

I need to find if date is within 2 ranges of dates or not.

For example, my account has two periods when it was active - from 1/1/2017 to 12/31/2017(range 1) and from 1/1/2019  to 12/31/2019(range 2). I need to find if OpportunityCloseDate was within range1/range2(Date= 6/27/2017 or 4/25/2019) or between these ranges(Date = 7/26/2018).

I have separate table for periods when account was active. Some accounts have one range but some have two ranges.

It would be great if there is a way to calculate this in PowerQuery.

Thank you in advance!

Regards,

Oleh

[Anonymous]

in the absence of more specific indications on the scope of application, you may have useful suggestions from a function like this

let
    range1 = {#date(2017,1,1),#date(2017,12,31)},
    range2 = {#date(2019,1,1),#date(2019,12,31)},

    inout=(dat)=> if (dat <range1{0} or dat > range2{1}) then "out" else if dat <range1{1} then "in range1" else if dat <range2{0}  then "in" else "in range2"

in
    inout

[Anonymous]

Thanks @Greg_Deckler, @Anonymous, @amitchandak 

Here you can find exaple. I'm trying to calculate column "WithinGoalPeriod" and main problem is to find if OpportunityCloseDate for opportunities for Account2 is within 1/1/2017-12/31/2017 and 1/1/2020-12/31/2020.

It will be great if there is a way to calculate it in PowerQuery. If not, DAX wil be ok too.

Extremely grateful for your help!

Account	GoalStartDate	GoalEndDate		Opportunity	Account	OpportunityCloseDate	WithinGoalPeriod
Acc1	1/1/2019	12/31/2019		Opp1	Acc1	4/16/2019	TRUE
Acc2	1/1/2017	12/31/2017		Opp2	Acc1	7/13/2020	FALSE
Acc2	1/1/2020	12/31/2020		Opp3	Acc2	5/19/2017	TRUE
Acc3	1/1/2018	12/31/2018		Opp4	Acc2	6/13/2018	FALSE
				Opp5	Acc2	2/15/2020	TRUE
				Opp6	Acc2	1/24/2022	FALSE
				Opp7	Acc3	6/18/2018	TRUE
				Opp8	Acc3	5/22/2017	FALSE

[Anonymous]

try this

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc47DoAgEEXRvVCbDDP8SwqtTEz8VMbKDbj/ShBxDBVTnNzHvovpulB0Ip5nfjSgBZIY0r3OWy+O7iHExAGqREime4jj8hlVTKYGMOSMazKaiS0Z9G3GsCFAU6f+GcsEgXQm1GZcMapM+Tr1z3gmaYbqh9/McQM=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Opportunity = _t, Account = _t, OpportunityCloseDate = _t, WithinGoalPeriod = _t]),

inout=(tab, datopp)=>
let 
rngs=Table.ToRows(tab[[GoalStartDate],[GoalEndDate]]),
inrng= List.Accumulate(rngs,false, (s,c)=>s or (datopp > c{0} and datopp < c{1}) )
in inrng,


    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Opportunity", type text}, {"Account", type text}, {"OpportunityCloseDate", type date}, {"WithinGoalPeriod", type logical}},"en-US"),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "inRange", (rowTableOpp)=>  inout(Table.SelectRows(ranges,(rowTableRanges)=> rowTableRanges[Account]=rowTableOpp[Account]),rowTableOpp[OpportunityCloseDate]))
in
    #"Added Custom"

[Anonymous]

Thank you all!

[ToddChitt · Accepted solution]

Seems all you need to do is join (MERGE) the two datasets in Power Query on the Account column. Note that if you have one Account that has either A) Multiple Opportunities or B) Multiple 'Active' ranges, then you will get multiple records for that combination. In fact, you will get the product (multiplication) of the number of Opportunities times the number of Ranges. But that's OK. In your sample data, Account 1 will have 2 records (1 Range * 2 Opp) and Account 2 will have 8 (2 Range * 4 Opp)

Then put in a calculated column that gives TRUE or FALSE based on if the Close Date was between the two Range Dates. 

Finally, filter for the TRUE's.

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format?

Check if this can help a bit -https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Greg_Deckler]

@Anonymous Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.