Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
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) )
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) )
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
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.
User | Count |
---|---|
102 | |
91 | |
87 | |
79 | |
71 |
User | Count |
---|---|
113 | |
105 | |
101 | |
75 | |
64 |