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 a...
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)
)
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.