Forum Discussion
How to find if date is between two ranges
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
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.
7 Replies
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.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
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 inoutThanks 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"
