Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric 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
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
Check out the May 2024 Power BI update to learn about new features.
User | Count |
---|---|
71 | |
43 | |
21 | |
21 | |
14 |
User | Count |
---|---|
125 | |
42 | |
39 | |
27 | |
24 |