Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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

1 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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





View solution in original post

7 REPLIES 7
Anonymous
Not applicable

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
Not applicable

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!

 

AccountGoalStartDateGoalEndDate OpportunityAccountOpportunityCloseDateWithinGoalPeriod
Acc11/1/201912/31/2019 Opp1Acc14/16/2019TRUE
Acc21/1/201712/31/2017 Opp2Acc17/13/2020FALSE
Acc21/1/202012/31/2020 Opp3Acc25/19/2017TRUE
Acc31/1/201812/31/2018 Opp4Acc26/13/2018FALSE
    Opp5Acc22/15/2020TRUE
    Opp6Acc21/24/2022FALSE
    Opp7Acc36/18/2018TRUE
    Opp8Acc35/22/2017FALSE
Anonymous
Not applicable

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"

 

 

image.png

Anonymous
Not applicable

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.




Did I answer your question? If so, mark my post as a solution. Also consider helping someone else in the forums!

Proud to be a Super User!





amitchandak
Super User
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

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Greg_Deckler
Community Champion
Community Champion

@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.



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
DAX For Humans

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.