Create date table between two dates and add a column from another table

[ClemFandango]

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

[Ashish_Mathur]

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.

[DataInsights · Accepted solution]

@ClemFandango,

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

[ClemFandango]

Amazing! thanks so much.

Sorry for the delay - i thought I had replied to this.