Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Create a calcuated Calender

Hi All,

 

need help with dax to create a Calculated Calender with first day of the month

 

Eg: Calcualted Table with fist day of the month

 

01/01/2021

02/01/2021

03/01/2021

....

 

Regards,

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    HiAnonymous,

     

    You can click New table on the Modeling ribbon and enter the following code:

     

    Dates = 
  GENERATE ( 
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ), 
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month = MONTH ( currentDay ) 
    VAR year = YEAR ( currentDay )
  RETURN ROW ( 
    "day", day, 
    "month", month, 
    "year", year )
  )

     

     

    Then you can try measure like this to meet your needs:

     

     

     

    Start of Month Dates =

  GENERATE (

    GENERATESERIES(1,12),

    VAR inc = [Value]

  RETURN ROW (

    "date", DATE(2020,inc,1)

    )

  )

     

     

    Then you can change the format of the dates.

    Hope it helps.



    Best Regards,
    Caitlyn Yan

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

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , You can create a calendar and filter It like

    filter(calendar(date(2020,1,1), date(2021,12,1)), day([date])=1)

     

    But my advice will be to have a complete calendar.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi Amith,

       

      need a single table with first day of the month in a calculated calender table. Could you pls advise.

       

      Regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    HiAnonymous,

     

    You can click New table on the Modeling ribbon and enter the following code:

     

    Dates = 
  GENERATE ( 
    CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2020, 12, 31 ) ), 
    VAR currentDay = [Date]
    VAR day = DAY( currentDay )
    VAR month = MONTH ( currentDay ) 
    VAR year = YEAR ( currentDay )
  RETURN ROW ( 
    "day", day, 
    "month", month, 
    "year", year )
  )

     

     

    Then you can try measure like this to meet your needs:

     

     

     

    Start of Month Dates =

  GENERATE (

    GENERATESERIES(1,12),

    VAR inc = [Value]

  RETURN ROW (

    "date", DATE(2020,inc,1)

    )

  )

     

     

    Then you can change the format of the dates.

    Hope it helps.



    Best Regards,
    Caitlyn Yan

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