Forum Discussion
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 (
'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.
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
5 Replies
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
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])}
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.
Hi,
That M code has to be written int he Query Editor. In the Query Editor, go to Add Column > Custom column
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.
