Forum Discussion

duggy's avatar
duggy
Advocate II
7 years ago
Solved

Generate date column using generate series

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 ( 'sp...
Need Help
  • v-danhe-msft's avatar
    v-danhe-msft
    7 years ago

    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's avatar
HotChilli
Community Champion
7 years ago

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.