Forum Discussion

nkasdali's avatar
nkasdali
Microsoft Employee
7 years ago
Solved

Dynamic parameters

Hi all,
in the aim to get a calculated calender, I'm using two parameters StartDate and EndDate.

Do you know how can I use these dynamically? I mean the first one will get current date - 2 years and the last current date +2 years?

Regards
Need Help
  • nkasdali's avatar
    nkasdali
    7 years ago

    Hi All,

     

    Thanks for your messages.

    I found a solution to my issue, i set in DAX my first date and my Last Date as a variable. Using these values, i started to create my calendar

     

    Calendar = 
var StartDate=min(Feuil1[Column2])
var EndDate=max(Feuil1[Column2])
var Days=CALENDAR(StartDate;EndDate)
RETURN ADDCOLUMNS (
    Days;
    "DateId";FORMAT([Date];"yyyy")&format([Date];"mm")&FORMAT([Date];"dd");
    "Year"; YEAR ( [Date] );
    "Quarter"; CONCATENATE("Q";FORMAT([Date];"q"));
    "Semester";CONCATENATE("S";ROUNDUP(MONTH([date])/6;0));
    "Month"; MONTH([Date]);
    "Month "; FORMAT([Date];"MM");
    "Month Name"; FORMAT([Date];"MMMM");
    "Week";WEEKNUM([Date]);
    "Day Week";FORMAT([Date];"w");
    "Day";FORMAT([Date];"dd");
    "Day Name";FORMAT([Date];"dddd");
    "CurrentDate";If(FORMAT([Date];"dd/mm/yyyy")=FORMAT(NOW();"dd/mm/yyyy");1;0);
    "CurrentMonth";If(FORMAT([Date];"mm/yyyy")=FORMAT(NOW();"mm/yyyy");1;0);
    "CurrentWeek";If(WEEKNUM([Date])=WEEKNUM(NOW());1;0);
    "CurrentYear";If(FORMAT([Date];"yyyy")=FORMAT(NOW();"yyyy");1;0)
)

3 Replies

  • Stachu's avatar
    Stachu
    Community Champion
    7 years ago

    in M you can use List.Dates, e.g.

    = List.Dates(#date(2017,1,1),730,#duration(1,0,0,0))

    creates a list of dates starting in 2017-01-01 and 730 days onwards

    with parameters you could create a list like this

    = List.Dates(StartDate,Duration.Days(EndDate-StartDate)+1,#duration(1,0,0,0))

    in DAX you can generate dynamic list to be used in calculations, but not as a loaded table

    • Chihiro's avatar
      Chihiro
      Solution Sage
      7 years ago

      FYI - You can create table using DAX.

       

      Ex: Go to Modeling tab ->"New Table".

      Enter following as formula

      DimDate = CALENDAR(Date(Year(Today())-2,1,1),Date(Year(today())+2,12,31))

       

      This will create DimDate table with Date column holding date value between Start & End dates.

      And I always recommend creating calender table for full year's dates (i.e. Jan 1 to Dec 31 of each year in calendar table), to ensure that all time intelligence functions return expected result.

       

  • nkasdali's avatar
    nkasdali
    Microsoft Employee
    7 years ago

    Hi All,

     

    Thanks for your messages.

    I found a solution to my issue, i set in DAX my first date and my Last Date as a variable. Using these values, i started to create my calendar

     

    Calendar = 
var StartDate=min(Feuil1[Column2])
var EndDate=max(Feuil1[Column2])
var Days=CALENDAR(StartDate;EndDate)
RETURN ADDCOLUMNS (
    Days;
    "DateId";FORMAT([Date];"yyyy")&format([Date];"mm")&FORMAT([Date];"dd");
    "Year"; YEAR ( [Date] );
    "Quarter"; CONCATENATE("Q";FORMAT([Date];"q"));
    "Semester";CONCATENATE("S";ROUNDUP(MONTH([date])/6;0));
    "Month"; MONTH([Date]);
    "Month "; FORMAT([Date];"MM");
    "Month Name"; FORMAT([Date];"MMMM");
    "Week";WEEKNUM([Date]);
    "Day Week";FORMAT([Date];"w");
    "Day";FORMAT([Date];"dd");
    "Day Name";FORMAT([Date];"dddd");
    "CurrentDate";If(FORMAT([Date];"dd/mm/yyyy")=FORMAT(NOW();"dd/mm/yyyy");1;0);
    "CurrentMonth";If(FORMAT([Date];"mm/yyyy")=FORMAT(NOW();"mm/yyyy");1;0);
    "CurrentWeek";If(WEEKNUM([Date])=WEEKNUM(NOW());1;0);
    "CurrentYear";If(FORMAT([Date];"yyyy")=FORMAT(NOW();"yyyy");1;0)
)