Forum Discussion

paris's avatar
paris
Helper V
6 years ago
Solved

DAX - Calender list

Hi All,

 

I like to make a list of year from 2010 to 2030. (2010,2011,2012... 2030)

CalendarTable = CALENDAR(year(2010),year(2030))
 
Please help.
 
ALl the best,
Paris

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I assuem you assign start date and end date. Then you can create a calculated table with DAX. Please refer to my sample below:

     

    MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])
     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    I assuem you assign start date and end date. Then you can create a calculated table with DAX. Please refer to my sample below:

     

    MonthTable = 
var FullCalendar = ADDCOLUMNS(CALENDAR("2016/1/1","2017/12/31"),"Month Number",MONTH([Date]),"Year",YEAR([Date]),"Year-Month",LEFT(FORMAT([Date],"yyyyMMdd"),6),"Month Name",FORMAT(MONTH([Date]),"MMM"),"Year-MonthName",YEAR([Date]) & " " & FORMAT(MONTH([Date]),"MMM"))
return 
SUMMARIZE(FullCalendar,[Month Number],[Year],[Year-Month],[Year-MonthName])
     

     

    • paris's avatar
      paris
      Helper V
      6 years ago

      Hello Vjnvinod,

       

      Thank you for your message.

      I tried it but it gave me daily data. I just want to have a list of year not daily. 

      Is there anyway I can do?

       

      All the best,

      Paris

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    paris , Create a new table

    Calendar Table = CALENDAR(date(2010,01,01),date(2030,12,31))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    • HenkM's avatar
      HenkM
      Frequent Visitor
      6 years ago

      Hello, I have a problem with the calender function. After adding your formula "Calender(date(2010,01,01), Date(2030,12,31)) i got the following error message: Too few arguments were passed to the date function. The minimum argument count for the function is 3.

      What could be the problem? My windows date format is european.