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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

Grouping by Date Ranges

I would like to group my data by date ranges. These ranges have a startdate and an enddate in a table along with a promotion code. My data contains production data that has a ship date that I would like to group the data into the date ranges for the promotion code by the ship date. Unfortunately, I cannot relate the two tables so I need a different method. All help and suggestions are appreciated.
1 ACCEPTED SOLUTION
Anonymous
Not applicable

I created a column in the Promo Table that had all dates and the corresponding promo (promodates) - this then has a field with the promo codes for each date. Related the workdate with the promodate with a many to many relationship filtering both ways and got the answer I was looking forExample2.gif

View solution in original post

5 REPLIES 5
AlB
Super User
Super User

Hi @Anonymous 

It would help if you provide some sample data and an example. Try creating a calculated column in your fist table:

RangeCol =
CALCULATE (
    DISTINCT ( Table2[Promotion Code] ),
    FILTER (
        Table2,
        Table2[StartDate] <= Table1[Ship date] && Table2[EndDate] > Table1[Ship date]
    )
)

Please mark the question solved when we get to the solution and consider kudoing if posts are helpful.

Cheers  Datanaut

 

 

 

Anonymous
Not applicable

AIB - Thanks so much for your help! After I was able to finally get the two tables related, I tried your code as in your previous post. However, when I hit enter at the end of typing my code I get the following message: 'A table of multiple values was supplied where a single value was expected." Still trying to remember how I can send you some sample tables. Thanks in advance!

The code is assuming no relationships

Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data). You have to share the URL to the file hosted elsewhere: Dropbox, Onedrive... or just upload the file to a site like tinyupload.com (no sign-up required).

Anonymous
Not applicable

So here are the two tables.   I need to know which PromoIDkey in the second table where the date from the first table (workdate) correlates to in the second table where it falls between the ScheduledStartDateID and the ScheduledEndDateID.  I hope this helps.   

 

tablestables

Anonymous
Not applicable

I created a column in the Promo Table that had all dates and the corresponding promo (promodates) - this then has a field with the promo codes for each date. Related the workdate with the promodate with a many to many relationship filtering both ways and got the answer I was looking forExample2.gif

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.