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

// Bovenstaande regel pakt de laagste datum uit de feitentabel (pas dit aan) en de laatste dag van volgend jaar.

 

“Jaar” , YEAR ([Date]) ,

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

“Maand” , MONTH([Date]),

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

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

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

“Dag” , DAY([Date]),

“Jaar-Maand” , (YEAR ([Date])*100) + MONTH([Date]),

“Jaar-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(),”Ja”,”Nee”),

“Afgesloten maanden”, IF([Date] >= DATE(YEAR(TODAY()),1,1) && [Date] <= TODAY() && MONTH([Date]) < MONTH(TODAY()),”Ja”,”Nee”),

“Afgesloten maanden jaren”, IF(MONTH([Date]) < MONTH(TODAY()),”Ja”,”Nee”),

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

“Vandaag”, IF([Date] = TODAY(), “Ja”, “Nee”),

“Gisteren”, IF([Date] = TODAY()-1, “Ja”, “Nee”),

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

“Vorige week”, IF(ROUNDUP(DATEDIFF( [Date] , TODAY() + (8 – WEEKDAY(TODAY(),2)),DAY) / 7,0) = 2, “Ja”, “Nee”),

“Deze maand”, IF(DATEDIFF([Date],TODAY(),MONTH) = 0, “Ja”, “Nee”),

“Vorige maand”, IF(DATEDIFF([Date],TODAY(),MONTH) = -1, “Ja”, “Nee”),

“Dit jaar”, IF(DATEDIFF([Date],TODAY(),YEAR) = 0, “Ja”, “Nee”)

)

DIM_DateFilters = UNION(FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[YearToDate],”Label”,”Year To Date”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[LastYearToDate],”Label”,”Last Year To Date”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Vandaag],”Label”,”Vandaag”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Gisteren],”Label”,”Gisteren”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Deze week],”Label”,”Deze week”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Deze week],”Label”,”Alle data”),[FilterColumn] IN {“Ja”,”Nee”}),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Afgesloten maanden],”Label”,”Afgesloten maanden”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Vorige maand],”Label”,”Vorige maand”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Deze maand],”Label”,”Deze maand”),[FilterColumn] = “Ja”),
FILTER(SELECTCOLUMNS(DIM_Kalender,”Date”,[Date],”FilterColumn”,[Dit jaar],”Label”,”Dit jaar”),[FilterColumn] = “Ja”))