Slicer Help on PowerBi Desktop - Dates

Hi Anonymous ,

To get the date of last week and this week, please try the following formula:

SpecialDates = 
var _datetable = DateTable2
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 _lastquarterstart = EDATE(_thisquarterstart, -3)
VAR _thisweek = WEEKNUM(TODAY(),2)

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]>=_thismonthstart),"Period","This Month","Order",4),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_lastmonthstart && [Date]<_thismonthstart),"Period","Last Month","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_lastquarterstart && [Date]<_thisquarterstart),"Period","Last Quarter","Order",7),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",8),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",9),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-60),"Period","Last 60 Days","Order",10),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-90),"Period","Last 90 Days","Order",11),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-120),"Period","Last 120 Days","Order",12),
    ADDCOLUMNS(FILTER(_datetable,WEEKNUM([Date],2)=_thisweek),"Period","This Week","Order",13),
    ADDCOLUMNS(FILTER(_datetable,WEEKNUM([Date],2)=_thisweek-1),"Period","Last Week","Order",14)
)

If you want to compare data between this month and last month, you can try adding the Period field to the Small multiples pane and then adjusting the number of rows and columns as needed.

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Anonymous ,

I'm very sorry that I did not consider the different years in my previous calculation. Please try the following formula:

SpecialDates = 
var _datetable = DateTable2
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 _lastquarterstart = EDATE(_thisquarterstart, -3)
VAR _thisweek = WEEKNUM(TODAY(),2)

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]>=_thismonthstart),"Period","This Month","Order",4),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_lastmonthstart && [Date]<_thismonthstart),"Period","Last Month","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_lastquarterstart && [Date]<_thisquarterstart),"Period","Last Quarter","Order",7),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisyearstart),"Period","This Year","Order",8),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-30),"Period","Last 30 Days","Order",9),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-60),"Period","Last 60 Days","Order",10),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-90),"Period","Last 90 Days","Order",11),
    ADDCOLUMNS(FILTER(_datetable,[Date]>_today-120),"Period","Last 120 Days","Order",12),
    ADDCOLUMNS(FILTER(_datetable,WEEKNUM([Date],2)=_thisweek && YEAR([Date])=_year),"Period","This Week","Order",13),
    ADDCOLUMNS(FILTER(_datetable,WEEKNUM([Date],2)=_thisweek-1 && YEAR([Date])=_year),"Period","Last Week","Order",14)
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz