DIM_Calendar = ADDCOLUMNS(CALENDAR(MIN(FactTable[Datum]),DATE(YEAR(TODAY())+1,12,31)),

// The above line picks the lowest date from the fact table (modify this) and the last day of next year.

 

“Year” , YEAR ([Date]) ,

“Quarter” , CONCATENATE(“Q”,ROUNDUP(MONTH([Date])/3,0)),

“Month” , MONTH([Date]),

“Month name”, IF(MONTH([Date]) = 1, “Jan,” IF(MONTH([Date]) = 2, “Feb,” IF(MONTH([Date]) = 3, “Maa”, IF(MONTH([Date]) = 4, “Apr”,IF(MONTH([Date]) = 5, “May,” IF(MONTH([Date]) = 6, “Jun,” IF(MONTH([Date]) = 7, “Jul,” IF(MONTH([Date]) = 8, “Aug,” IF(MONTH([Date]) = 9, “Sep,” IF(MONTH([Date]) = 10, “Oct,” IF(MONTH([Date]) = 11, “Nov,” IF(MONTH([Date]) = 12, “Dec,” BLANK ())))))))))))),

“Week” , WEEKNUM([Date],21),

“Weekday” , WEEKDAY([Date],2),

“Day” , DAY([Date]),

“Year-Month” , (YEAR([Date])*100) + MONTH([Date]),

“Year-Week” , (YEAR([Date])*100) + WEEKNUM([Date],21),

“YearsBack” , CONVERT(YEAR(TODAY())) – YEAR([Date]),STRING),

“MonthsBack” , CONVERT(DATEDIFF( [Date],TODAY(),MONTH),STRING),

“WeeksBack” , CONVERT(ROUNDUP(DATEDIFF( [Date], TODAY() + (8 – WEEKDAY(TODAY(),2)),DAY) / 7,0)-1,STRING),

“DaysBack” , DATEDIFF( [Date],TODAY(),DAY),

“YearToDate”, IF([Date] >= DATE(YEAR(TODAY()),1,1) && [Date] <= TODAY(), “Yes”, “No”),

“Finished months,” IF([Date] >= DATE(YEAR(TODAY()),1,1) && [Date] <= TODAY() && MONTH([Date]) < MONTH(TODAY()), “Yes”, “No”),

“Finished months years”, IF(MONTH([Date]) < MONTH(TODAY()), “Yes”, “No”),

“LastYearToDate”, IF([Date] >= DATE(YEAR(TODAY())-1,1,1) && [Date] <= DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY())), “Yes”, “No”),

“Today,” IF([Date] = TODAY(), “Yes,” “No”),

“Yesterday,” IF([Date] = TODAY()-1, “Yes,” “No”),

“This week,” IF(ROUNDUP(DATEDIFF( [Date], TODAY() + (8 – WEEKDAY(TODAY(),2)),DAY) / 7,0) = 1, “Yes,” “No”),

“Last week,” IF(ROUNDUP(DATEDIFF( [Date], TODAY() + (8 – WEEKDAY(TODAY(),2)),DAY) / 7,0) = 2, “Yes,” “No”),

“This month”, IF(DATEDIFF([Date],TODAY(),MONTH) = 0, “Yes”, “No”),

“Previous month”, IF(DATEDIFF([Date],TODAY(),MONTH) = -1, “Yes”, “No”),

“This year,” IF(DATEDIFF([Date],TODAY(),YEAR) = 0, “Yes,” “No”)

)

DIM_DateFilters = UNION(FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[YearToDate], “Label”, “Year To Date”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[LastYearToDate], “Label”, “Last Year To Date”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Vandaag], “Label”, “Today”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Gisteren], “Label”, “Yesterday”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Deze week], “Label”, “This Week”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Deze week], “Label”, “All Dates”),[FilterColumn] IN {“Yes”, “No”}),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Afgesloten maanden], “Label”, “Finished months”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Vorige maand], “Label”, “Previous Month”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Deze maand], “Label”, “This Month”),[FilterColumn] = “Yes”),
FILTER(SELECTCOLUMNS(DIM_Calendar, “Date”,[Date], “FilterColumn”,[Dit jaar], “Label”, “This Year”),[FilterColumn] = “Yes”))