Forum Discussion
How to find if date is between two ranges
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.
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
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 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"Thank you all!
- ToddChitt
Super User
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.