Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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
Solved! Go to 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.
Proud to be a Super User! | |
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!
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.
Proud to be a Super User! | |
@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
@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.