Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Dan80
Helper II
Helper II

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
MarcelBeug
Community Champion
Community Champion

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)

View solution in original post

15 REPLIES 15
Kasunpathirana
Resolver I
Resolver I

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

Phil_Seamark
Employee
Employee

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!

Thanks heaps for your help, appreciate it
Sean
Community Champion
Community Champion

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

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 🙂


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

Proud to be a Datanaut!

@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

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!

Sean
Community Champion
Community Champion

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 

MarcelBeug
Community Champion
Community Champion

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)
Anonymous
Not applicable

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

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.

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

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

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

Sensational, thanks so much for your help!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.