Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- D_Date =
- ADDCOLUMNS (
- CALENDAR ( DATE(2020, 4, 1), TODAY() ),
- "DimYear", YEAR ( [Date] ),
- "DimMonth", FORMAT(MONTH ( [Date] ), "00"),
- "MonthNumber", MONTH([Date]),
- "DimWeekNum", WEEKNUM ( [Date], 1 ),
- "FiscalYear", VAR YearPart = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)RETURN "FY" & RIGHT(YearPart, 2) & "/" & RIGHT(YearPart + 1, 2),
- "CalendarDay", FORMAT(DAY([Date]), "00"),
- "CalendarMonthName", FORMAT([Date], "MMMM"),
- "FiscalMonthNumber",
- VAR MonthNum = MONTH([Date])
- RETURN FORMAT(IF(MonthNum > 3, MonthNum - 3, MonthNum + 9), "00"),
- "YearMonth", FORMAT([Date], "MMMM YYYY"),
- "DaysAgo", DATEDIFF([Date], TODAY(), DAY),
- "WeekGroup", SWITCH(
- TRUE(),
- DATEDIFF([Date], TODAY(), DAY) >= 0 && DATEDIFF([Date], TODAY(), DAY) <= 7, "0-7 Days",
- DATEDIFF([Date], TODAY(), DAY) >= 8 && DATEDIFF([Date], TODAY(), DAY) <= 14, "7-14 Days",
- DATEDIFF([Date], TODAY(), DAY) >= 15 && DATEDIFF([Date], TODAY(), DAY) <= 30, "This Month",
- "Older"
- ),
- "shortYearMonth", LEFT(FORMAT([Date], "MMMM"), 3) & " " & RIGHT(FORMAT([Date], "YYYY"), 2),
- "FiscalYearMonth",
- VAR FYear = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
- VAR FMonth = FORMAT(IF(MONTH([Date]) > 3, MONTH([Date]) - 3, MONTH([Date]) + 9), "00")
- RETURN FORMAT(FYear, "0000") & FMonth,
- "FiscalDate",
- VAR FYear = IF(MONTH([Date]) >= 4, YEAR([Date]), YEAR([Date]) - 1)
- VAR FMonth = FORMAT(IF(MONTH([Date]) > 3, MONTH([Date]) - 3, MONTH([Date]) + 9), "00")
- VAR FDay = FORMAT(DAY([Date]), "00")
- RETURN FYear & FMonth & FDay,
- "StartOfMonth", DATE(YEAR([Date]), MONTH([Date]), 1),
- "Month_YY",
- CONCATENATE(
- CONCATENATE(
- LEFT(FORMAT([Date], "MMMM"), 1),
- REPT(UNICHAR(8203), 1)
- ),
- CONCATENATE(
- " ",
- RIGHT(FORMAT([Date], "yyyy"), 2)
- )
- )
- )
Advertisement
Add Comment
Please, Sign In to add comment