Forum Discussion
Adding WTD date range to special date slicer
So I have this DAX Expression for my customized Date range slicer. I want to add a WTD date range as well.
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)
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(_datetable,"Period","Custom Range","Order",9)
)
This is my DAX for WTD:
Start of Week =
var CurrentDate=LASTDATE('NewDateRange'[Date])
var DayNumberOfWeek=WEEKDAY(LASTDATE('NewDateRange'[Date]),3)
return
DATEADD(
CurrentDate,
-1*DayNumberOfWeek,
DAY)
How would I add this to the above Special Dates DAX so when I select "This Week" data range it will show all data from the start of the week to TODAY()?
Hello Anonymous,
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.
2 Replies
Hello Anonymous,
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.