cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Dan80 Regular Visitor
Regular Visitor

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 this can be done???
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Convert date ranges into list of dates?

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.

Specializing in Power Query Formula Language (M)
11 REPLIES 11
Super User
Super User

Re: Convert date ranges into list of dates?

Judging from the answer you just provided on another post I assumed you don't want this done with DAX Smiley Happy

 

I think I've seen @MarcelBeug do this with M 

Super User
Super User

Re: Convert date ranges into list of dates?

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.

Specializing in Power Query Formula Language (M)
Phil_Seamark Super Contributor
Super Contributor

Re: Convert date ranges into list of dates?

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Dan80 Regular Visitor
Regular Visitor

Re: Convert date ranges into list of dates?

Sensational, thanks so much for your help!
Dan80 Regular Visitor
Regular Visitor

Re: Convert date ranges into list of dates?

Thanks heaps for your help, appreciate it
Super User
Super User

Re: Convert date ranges into list of dates?

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 Smiley Happy 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 Super Contributor
Super Contributor

Re: Convert date ranges into list of dates?

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 Smiley Happy


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Super User
Super User

Re: Convert date ranges into list of dates?

@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!

Smiley Happy

 

Mystery solved! Smiley Happy

Phil_Seamark Super Contributor
Super Contributor

Re: Convert date ranges into list of dates?

Cool, I'm having a closer look at the timings of the two approaches.  The GENERATE function is more efficient because it reduces the number of records it reads from the date table to only those required from the start, whereas the CROSS JOIN function reads more than is necessary and then discards them at the FILTER stage.

 

I managed to get the CROSS FILTER down to 5ms doing this though....

 

 

my Table = 
var maxdate = max(Campaigns[End]) var mindate = min(Campaigns[Start]) var datesfiltered = CALCULATETABLE('Calendar Table', 'Calendar Table'[Date] <= maxdate && 'Calendar Table'[Date] >= mindate ) return SELECTCOLUMNS ( FILTER ( CROSSJOIN ( 'Campaigns', datesfiltered ), 'Campaigns'[Start] <= 'Calendar Table'[Date] && Campaigns[End] >= 'Calendar Table'[Date] ), "Campaign", [Campaigns], "Active Date", 'Calendar Table'[Date] )

 

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!