Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |