Forum Discussion

Dan80's avatar
Dan80
Icon for Helper II rankHelper II
9 years ago
Solved

Convert date ranges into list of dates?

Hi all, I have a table with 2 columns, start date and end date of sales campaigns but I need to convert the range into a list of dates, ie one column of all dates we have been on sale. Any ideas how t...
Need Help
  • MarcelBeug's avatar
    MarcelBeug
    9 years ago

    In M it can be done in various ways but the easiest would be to:
    - convert start and end dates to numbers,
    - create lists from those numbers,
    - expand the lists and
    - convert the numbers back to dates

    as demonstrated in this short video.

Phil_Seamark's avatar
Phil_Seamark
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

If you did want a DAX solution, 

 

Make sure you have a Dates table with a column called Date

 

Then you can create the following table if your base table looks like this :

 

Campaign   StartDate   EndDate

A1/01/201717/01/2017
B5/01/201712/01/2017

 

 

Campaigns Expanded = SELECTCOLUMNS(
                         FILTER(
                         CROSSJOIN('Campaigns',dates),
                         'Campaigns'[StartDate]<='Dates'[Date]
                         && 'Campaigns'[EndDate] >= 'Dates'[Date]
                         ),
                         "Campaign" , [Campaign] ,
                         "Active Date" , 'Dates'[Date])
Dan80's avatar
Dan80
Icon for Helper II rankHelper II
9 years ago
Thanks heaps for your help, appreciate it
  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    Hi  Phil_Seamark  I tried your solution with this data sample?

     

    CampaignsStartEnd

    A1/1/20171/4/2017
    B1/10/20171/13/2017
    C2/3/20172/5/2017
    D2/22/20173/1/2017
    E1/1/20173/1/2017

     

    With MarcelBeug's solution :smileyhappy: I get 79 rows

     

    My way of doing this with DAX also gets me 79 rows!

    (you do need a Calendar Table not connected to the Campaigns table)

     

    Campaigns Table =
SUMMARIZE (
    GENERATE (
        Campaigns,
        CALCULATETABLE (
            VALUES ( 'Calendar Table'[Date] ),
            DATESBETWEEN ( 'Calendar Table'[Date], 'Campaigns'[Start], 'Campaigns'[End] )
        )
    ),
    'Calendar Table'[Date],
    'Campaigns'[Campaigns]
)

    Your formula generates (3,705 rows) ?

     

    Campaigns Phil = 
SELECTCOLUMNS (
    FILTER (
        CROSSJOIN ( 'Campaigns', 'Calendar Table' ),
        'Campaigns'[Start] <= 'Calendar Table'[Date]
            && Campaigns[End] >= 'Calendar Table'[Date]
    ),
    "Campaign", [Campaigns],
    "Active Date", 'Calendar Table'[Date]
)
    • Phil_Seamark's avatar
      Phil_Seamark
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hmm, that's odd.  I just tried and it also got 79 rows.  I can upload a PBIX file if interested.

       

      I also tried both approaches out in DaxStudio to check the timings to see which was quicker.

       

      MarcelBeug query took just 8 ms to produce the 79 rows wheras my approch took 39 ms to produce the 79 rows, so I reckon the GENERATE function is the way to go :)

      • Sean's avatar
        Sean
        Icon for Community Champion rankCommunity Champion
        9 years ago

        Phil_SeamarkI got it! Go to the Query Editor and apply MarcelBeug's solution to the original Campaigns table!

         

        Then look at table created with your formula from 79 rows it goes to 3,705 rows!

         

        However NOTE that the GENERATE table is not affected by this!

         

        I was testing MarcelBeug's solution first, then I added my GENERATE table and then yours in the same file in this order!

        :smileyhappy:

         

        Mystery solved! :smileyhappy: