Forum Discussion

spandy34's avatar
spandy34
Responsive Resident
3 years ago
Solved

DAX Month Date Table

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 

Need Help
  • tamerj1's avatar
    tamerj1
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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's avatar
    Anonymous
    3 years ago

    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's avatar
    tamerj1
    3 years ago

    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] )
    )

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

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

        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] )
        )

  • parry2k's avatar
    parry2k
    Super User
    3 years ago

    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 )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

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

      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?

  • tamerj1's avatar
    tamerj1
    Community Champion
    3 years ago

    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] )
    )