Convert date ranges into list of dates?

[Dan80]

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???

[Kasunpathirana]

Wow @MarcelBeug The Saviour this is great Trick. Thanks for this 

[Phil_Seamark]

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

A	1/01/2017	17/01/2017
B	5/01/2017	12/01/2017

Campaigns Expanded = SELECTCOLUMNS(
                         FILTER(
                         CROSSJOIN('Campaigns',dates),
                         'Campaigns'[StartDate]<='Dates'[Date]
                         && 'Campaigns'[EndDate] >= 'Dates'[Date]
                         ),
                         "Campaign" , [Campaign] ,
                         "Active Date" , 'Dates'[Date])

[Dan80]

Thanks heaps for your help, appreciate it

[Sean]

Hi  @Phil_Seamark  I tried your solution with this data sample?

CampaignsStartEnd

A	1/1/2017	1/4/2017
B	1/10/2017	1/13/2017
C	2/3/2017	2/5/2017
D	2/22/2017	3/1/2017
E	1/1/2017	3/1/2017

With @MarcelBeug's solution 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]

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]

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

Mystery solved!

[Phil_Seamark]

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

[Sean]

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

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

[MarcelBeug · Accepted solution]

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.

[Anonymous]

Do you know how this would work with DateTime? I get a lot of errors when I try it this way

[yashasvi]

I have a date range which goes in minutes at an increment of 15 minutes. When i use this method to convert in whole numbers. It takes away the minutes and reset every date to 12:00 AM. I have tried to use decimal instead of whole numbers but still facing error.

Could you please provide the solution if the dates have minutes.

[Dan80]

Hi @yashasvi 

You could split the process in two.  Convert your dates into a list first using {[Date From]..[Date To]}.

Then once you have that list, do another list {[0]..[96]} this represents the minutes i.e. there are 96 * 15 mintues = 24 hours.  Then multiply the minutes 0.0104166666666667, this is the result of =15/(24*60) which will get you the day and minute value (at 15 minute) intervals.

Add the date and minutes column together and covert to date & time

Hope that helps

[yashasvi]

Thanks for the quick response @Dan80 . When I tried to use your M query to handle Dates which has minutes. It is showing error. Perhaps because when the dates are in minutes,then you you need to convert dates into fixed decimal numbers and not the whole numbers. When I applied you M query(which works perfect with whole numbers) to the decimal numbers,it is not able to handle it. I tried to fiddle with your M query and tried different formats before [Date From]..[Date to] but couldnt succeed.

Could you please share the M query which can handle the dates in minutes? By that I mean the M query which could handle the decimal (maybe fixed decimals).

Regards

[Jmenas]

@MarcelBeug thanks a lot whas a really precise and fast solution! 

[Dan80]

Sensational, thanks so much for your help!