Forum Discussion
Create date table between two dates and add a column from another table
Hi there,
I would like to create a new date table for every end of month between 31/01/2018 & 31/12/2028, but including the 'country consolidated' field as an extra column found within 'table1' below. The 'country consolidated' field contains just 5 countries, 'England', 'Scotland', 'international' etc.
This is an example of 'table1'
The new table would hopefully look something like this and continue to generate dates between 31/01/2018 & 31/12/2028 for each of the 5 countries found within Table1 'Country Consolidated'.
| Date | Country |
| 31/01/2018 | England |
| 28/02/2018 | England |
| 31/03/2018 | England |
| 30/04/2018 | England |
Any idea on the DAX required to generate this date table?
Any help eternally appreciated
Try this calculated table:
DatesWithCountry = VAR vCalendar = CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2028, 12, 31 ) ) VAR vCalendarEOM = FILTER ( vCalendar, [Date] = EOMONTH ( [Date], 0 ) ) VAR vCountries = VALUES ( Table1[Country Consolidated] ) VAR vResult = GENERATE ( vCalendarEOM, vCountries ) RETURN vResult
3 Replies
Try this calculated table:
DatesWithCountry = VAR vCalendar = CALENDAR ( DATE ( 2018, 1, 1 ), DATE ( 2028, 12, 31 ) ) VAR vCalendarEOM = FILTER ( vCalendar, [Date] = EOMONTH ( [Date], 0 ) ) VAR vCountries = VALUES ( Table1[Country Consolidated] ) VAR vResult = GENERATE ( vCalendarEOM, vCountries ) RETURN vResultAmazing! thanks so much.
Sorry for the delay - i thought I had replied to this.
Hi,
Would you be OK with a Power Query solution? If yes, then share Table1 in a format that can be pasted in an MS Excel file.
