Grouping by Date Ranges

[Anonymous]

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.

[AlB]

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  

[Anonymous]

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!

[AlB]

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]

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.   

tables

[Anonymous · Accepted solution]

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 for