Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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.
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

5 Replies

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago
      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's avatar
        AlB
        Icon for Community Champion rankCommunity Champion
        7 years ago

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