DAX Month Date Table

[spandy34]

How would I create a date table in Dax that lists the start date and end date of each month from 01/07/2007 to 31/12/2099 similar to the format below?  Thank you in advance

@amitchandak @tamerj1 @danextian @goncalogeraldes 

[tamerj1 · Accepted solution]

Hi @spandy34 

please try

Month Date =
VAR MinDate =
DATE ( 2007, 7, 1 )
VAR MaxDate =
DATE ( 2099, 12, 31 )
VAR Months =
DATEDIFF ( MinDate, MaxDate, MONTH )
VAR T1 =
GENERATESERIES ( 0, Months, 1 )
RETURN
SELECTCOLUMNS (
T,
"StartDate", EDATE ( Mindate, [Value] ),
"EndDate", EOMONTH ( MinDate, [Value] )
)

[tamerj1 · Accepted solution]

@spandy34 

Sorry its a typo in the variable name

Month Date =
VAR MinDate =
DATE ( 2007, 7, 1 )
VAR MaxDate =
DATE ( 2099, 12, 31 )
VAR Months =
DATEDIFF ( MinDate, MaxDate, MONTH )
VAR T =
GENERATESERIES ( 0, Months, 1 )
RETURN
SELECTCOLUMNS (
T,
"StartDate", EDATE ( Mindate, [Value] ),
"EndDate", EOMONTH ( MinDate, [Value] )
)

[Anonymous · Accepted solution]

Hi  @spandy34 ,

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
CALENDAR(
    DATE(2007,1,1),
    DATE(2099,12,31))
var _table2=
ADDCOLUMNS(
    _table1,"Enddate",EOMONTH([Date],0))
return
FILTER(
    _table2,   [Date]=MINX(FILTER(_table2,YEAR([Date])=YEAR(EARLIER([Date]))&&MONTH([Date])=MONTH(EARLIER([Date]))),[Date]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi  @tamerj1 ,

Sorry, this was an oversight on my part, I didn't think about performance, this does cause the compute table to be iterated twice, resulting in a waste of resources.

Thanks for the heads up, I'm glad you made a correction to my problem, I'll pay more attention to this area in the future.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more

[tamerj1 · Accepted solution]

@spandy34 
Please refer to attached sample file with the solution

Month Date = 
VAR MinDate =
    DATE ( 2007, 7, 1 )
VAR MaxDate =
    DATE ( 2099, 12, 31 )
VAR Months =
    DATEDIFF ( MinDate, MaxDate, MONTH )
VAR T =
    GENERATESERIES ( 0, Months, 1 )
RETURN
    SELECTCOLUMNS (
        T,
        "StartDate", EDATE ( Mindate, [Value] ),
        "EndDate", EOMONTH ( MinDate, [Value] )
    )

[Anonymous · Accepted solution]

Hi  @spandy34 ,

Here are the steps you can follow：

1. Create calculated table.

Table =
var _table1=
CALENDAR(
    DATE(2007,1,1),
    DATE(2099,12,31))
var _table2=
ADDCOLUMNS(
    _table1,"Enddate",EOMONTH([Date],0))
return
FILTER(
    _table2,   [Date]=MINX(FILTER(_table2,YEAR([Date])=YEAR(EARLIER([Date]))&&MONTH([Date])=MONTH(EARLIER([Date]))),[Date]))

2. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tamerj1]

Hi @Anonymous 
That would definitely work. But why to create a 35k rows calendar table then iterate over it two times, first time to add a 35k rows column and 2nd time to filter it down 1k rows, while the same can be achieved in one iteration over a 1k rows series table which is 315 times faster?

[parry2k]

@spandy34 have you tried what I posted?

[parry2k]

@spandy34 of simply add two columns in the date dimension using following code:

Start of Month = EOMONTH ( Calendar[Date], -1 ) + 1

End of Month = EOMONTH ( Calendar[Date], 0 )

[tamerj1 · Accepted solution]

Hi @spandy34 

please try

Month Date =
VAR MinDate =
DATE ( 2007, 7, 1 )
VAR MaxDate =
DATE ( 2099, 12, 31 )
VAR Months =
DATEDIFF ( MinDate, MaxDate, MONTH )
VAR T1 =
GENERATESERIES ( 0, Months, 1 )
RETURN
SELECTCOLUMNS (
T,
"StartDate", EDATE ( Mindate, [Value] ),
"EndDate", EOMONTH ( MinDate, [Value] )
)

[spandy34]

Hi

I get this message

[tamerj1 · Accepted solution]

@spandy34 

Sorry its a typo in the variable name

Month Date =
VAR MinDate =
DATE ( 2007, 7, 1 )
VAR MaxDate =
DATE ( 2099, 12, 31 )
VAR Months =
DATEDIFF ( MinDate, MaxDate, MONTH )
VAR T =
GENERATESERIES ( 0, Months, 1 )
RETURN
SELECTCOLUMNS (
T,
"StartDate", EDATE ( Mindate, [Value] ),
"EndDate", EOMONTH ( MinDate, [Value] )
)