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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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