Guest User

DAX Date table

a guest
Jul 3rd, 2025
11
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 2.26 KB | None | 0 0
  1.  
  2. D_Date =
  3.     ADDCOLUMNS (
  4.         CALENDAR ( DATE(2020, 4, 1), TODAY() ),
  5.         "DimYear", YEAR ( [Date] ),
  6.         "DimMonth", FORMAT(MONTH ( [Date] ), "00"),
  7.         "MonthNumber", MONTH([Date]),
  8.         "DimWeekNum", WEEKNUM ( [Date], 1 ),
  9.         "FiscalYear", VAR YearPart = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)RETURN "FY" & RIGHT(YearPart, 2) & "/" & RIGHT(YearPart + 1, 2),
  10.  
  11.         "CalendarDay", FORMAT(DAY([Date]), "00"),
  12.         "CalendarMonthName", FORMAT([Date], "MMMM"),
  13.         "FiscalMonthNumber",
  14.             VAR MonthNum = MONTH([Date])
  15.             RETURN FORMAT(IF(MonthNum > 3, MonthNum - 3, MonthNum + 9), "00"),
  16.         "YearMonth", FORMAT([Date], "MMMM YYYY"),
  17.         "DaysAgo", DATEDIFF([Date], TODAY(), DAY),
  18.         "WeekGroup", SWITCH(
  19.             TRUE(),
  20.             DATEDIFF([Date], TODAY(), DAY) >= 0 && DATEDIFF([Date], TODAY(), DAY) <= 7, "0-7 Days",
  21.             DATEDIFF([Date], TODAY(), DAY) >= 8 && DATEDIFF([Date], TODAY(), DAY) <= 14, "7-14 Days",
  22.             DATEDIFF([Date], TODAY(), DAY) >= 15 && DATEDIFF([Date], TODAY(), DAY) <= 30, "This Month",
  23.             "Older"
  24.         ),
  25.         "shortYearMonth", LEFT(FORMAT([Date], "MMMM"), 3) & " " & RIGHT(FORMAT([Date], "YYYY"), 2),
  26.         "FiscalYearMonth",
  27.             VAR FYear = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
  28.             VAR FMonth = FORMAT(IF(MONTH([Date]) > 3, MONTH([Date]) - 3, MONTH([Date]) + 9), "00")
  29.             RETURN FORMAT(FYear, "0000") & FMonth,
  30.             "FiscalDate",
  31.             VAR FYear = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
  32.             VAR FMonth = FORMAT(IF(MONTH([Date]) > 3, MONTH([Date]) - 3, MONTH([Date]) + 9), "00")
  33.             VAR FDay = FORMAT(DAY([Date]), "00")
  34.             RETURN FYear & FMonth & FDay,
  35.  
  36.         "StartOfMonth", DATE(YEAR([Date]), MONTH([Date]), 1),
  37.         "Month_YY",
  38.     CONCATENATE(
  39.     CONCATENATE(
  40.         LEFT(FORMAT([Date], "MMMM"), 1),
  41.         REPT(UNICHAR(8203), 1)
  42.     ),
  43.     CONCATENATE(
  44.         " ",
  45.         RIGHT(FORMAT([Date], "yyyy"), 2)
  46.     )
  47. )
  48.  
  49.  
  50. )
  51.  
Advertisement
Add Comment
Please, Sign In to add comment