Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Solved! Go to Solution.
Hello @kboud3,
Can you please try to create a new row for "This Week" and set its period to "Week-to-Date":
NewSpecialDates =
VAR _datetable = NewDateRange
VAR _today = TODAY()
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _thismonthstart = DATE(_year, _month, 1)
VAR _thisyearstart = DATE(_year, 1, 1)
VAR _lastmonthstart = EDATE(_thismonthstart, -1)
VAR _lastmonthend = _thismonthstart - 1
VAR _thisquarterstart = DATE(YEAR(_today), SWITCH(true, _month > 9, 10, _month > 6, 7, _month > 3, 4, 1), 1)
VAR _startOfWeek =
VAR CurrentDate = LASTDATE('NewDateRange'[Date])
VAR DayNumberOfWeek = WEEKDAY(LASTDATE('NewDateRange'[Date]), 3)
RETURN DATEADD(CurrentDate, -1 * DayNumberOfWeek, DAY)
RETURN UNION(
ADDCOLUMNS(FILTER(_datetable, [Date] = _today), "Period", "Today", "Order", 1),
ADDCOLUMNS(FILTER(_datetable, [Date] = _today - 1), "Period", "Yesterday", "Order", 2),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _today - 7), "Period", "Last 7 Days", "Order", 3),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _today - 14), "Period", "Last 2 Weeks", "Order", 4),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thismonthstart && [Date] <= TODAY()), "Period", "This Month", "Order", 6),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thisyearstart), "Period", "This Year", "Order", 7),
ADDCOLUMNS(FILTER(_datetable, [Date] > _today - 30), "Period", "Last 30 Days", "Order", 8),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _startOfWeek && [Date] <= TODAY()), "Period", "This Week", "Order", 5),
ADDCOLUMNS(_datetable, "Period", "Custom Range", "Order", 9)
)
Should you require further assistance please do not hesitate to reach out to me.
Hello @kboud3,
Can you please try to create a new row for "This Week" and set its period to "Week-to-Date":
NewSpecialDates =
VAR _datetable = NewDateRange
VAR _today = TODAY()
VAR _month = MONTH(TODAY())
VAR _year = YEAR(TODAY())
VAR _thismonthstart = DATE(_year, _month, 1)
VAR _thisyearstart = DATE(_year, 1, 1)
VAR _lastmonthstart = EDATE(_thismonthstart, -1)
VAR _lastmonthend = _thismonthstart - 1
VAR _thisquarterstart = DATE(YEAR(_today), SWITCH(true, _month > 9, 10, _month > 6, 7, _month > 3, 4, 1), 1)
VAR _startOfWeek =
VAR CurrentDate = LASTDATE('NewDateRange'[Date])
VAR DayNumberOfWeek = WEEKDAY(LASTDATE('NewDateRange'[Date]), 3)
RETURN DATEADD(CurrentDate, -1 * DayNumberOfWeek, DAY)
RETURN UNION(
ADDCOLUMNS(FILTER(_datetable, [Date] = _today), "Period", "Today", "Order", 1),
ADDCOLUMNS(FILTER(_datetable, [Date] = _today - 1), "Period", "Yesterday", "Order", 2),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _today - 7), "Period", "Last 7 Days", "Order", 3),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _today - 14), "Period", "Last 2 Weeks", "Order", 4),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thismonthstart && [Date] <= TODAY()), "Period", "This Month", "Order", 6),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _thisyearstart), "Period", "This Year", "Order", 7),
ADDCOLUMNS(FILTER(_datetable, [Date] > _today - 30), "Period", "Last 30 Days", "Order", 8),
ADDCOLUMNS(FILTER(_datetable, [Date] >= _startOfWeek && [Date] <= TODAY()), "Period", "This Week", "Order", 5),
ADDCOLUMNS(_datetable, "Period", "Custom Range", "Order", 9)
)
Should you require further assistance please do not hesitate to reach out to me.
Thank you! That worked