Generate date column using generate series

[duggy]

Hi,

I have the following formula that generates a new table with a row for each day between a start and end date from a given table:

Events Table = 
SELECTCOLUMNS (
    GENERATE (
        'specialdays',
        GENERATESERIES (
            CALCULATE ( VALUES ( 'specialdays'[startspecialdaydate] ) ),
            CALCULATE ( VALUES ( 'specialdays'[Endspecialdaydate] ) )
        )
    ),
    "locationId", 'specialdays'[locationId],
    "specialday", 'specialdays'[specialdayname]
)

the result is:

StoreId	specialdayname
7601	Mother's Day
7601	Mother's Day
7601	Mother's Day
7601	Mother's Day

That is all good and well and seems to be working fine. However i want to add a column with the subsequent date of the generatedseries. So if I have a row in the table:

LocationId	specialdayname	startspecialdaydate	Endspecialdaydate
7601	Mother's Day	Thursday, 05 July 2018	Sunday, 08 July 2018

Then the result should be:

StoreId	specialdayname	Date
7601	Mother's Day	05-Jul-18
7601	Mother's Day	06-Jul-18
7601	Mother's Day	07-Jul-18
7601	Mother's Day	08-Jul-18

Can anyone suggest how I can achieve this?

Much appreciated.

[v-danhe-msft · Accepted solution]

Hi @duggy,

Based on my test, you could refer to below steps:

Create a calender table:

Date Table = CALENDAR(MAX('Table2'[startspecialdaydate]),MAX('Table2'[Endspecialdaydate]))

Create a new table:

Result Table = SUMMARIZECOLUMNS('Table1'[StoreId],'Table1'[specialdayname],'Date Table'[Date])

Result:

You could also download the pbix file to have a view.

Regards,

Daniel He

[HotChilli]

It might not be exactly what you're looking for but if we start with your 'specialdays' table, create a calendar table on the fly and do a cross-join...

SpecDateTable = 
         VAR Mday = FILTER(specialdays, specialdays[Specialdayname] = "Mother's Day")
	 VAR MdayData = SELECTCOLUMNS(Mday, "ID" , specialdays[LocationId], "DayName", specialdays[Specialdayname])
	 VAR Calt  = CALENDAR(SELECTCOLUMNS(Mday, "StartDay", specialdays[Startspecialdaydate]),
			SELECTCOLUMNS(Mday, "EndDay", specialdays[Endspecialdaydate]))
	
	RETURN GENERATE(MdayData, Calt)

Should give you the result you want.

[Ashish_Mathur]

Hi,

As long as data in the 3rd and 4th columns are actual dates, you should be able to get your desired result by using the following M code

={Number.From([startspecialdaydate[)..Number.From([Endspecialdaydate])}

[duggy]

Hi,

Sorry, not following. Are you saying to create a new column in the specialdays table and use the code:

theeventdates = {Number.From([startspecialdaydate])..Number.From([Endspecialdaydate])}

I get the syntax for 'From' is incorrect when I try this.

[Ashish_Mathur]

Hi,

That M code has to be written int he Query Editor.  In the Query Editor, go to Add Column > Custom column