Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Slicer Help on PowerBi Desktop - Dates

I have created a report that gives me analysis on ticket trends based on Today, Month, Year, Quater as shown below.   Below is the code I have used and it works perfectly as per what I need a...
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    5 years ago

    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.

     

  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    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

v-kkf-msft's avatar
v-kkf-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

When you select “This Week”, do all the dates in the associated table display correctly?

 

 

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Yes, thats exactly how it is created.

 

The code remains same for the Special table no change to that but below is the screenshots of the visualization (correct and incorrect one), models and date table columns.

 

Not sure why its considering 2020 dates as well

This Week Incorrect one

 

Correct one as shown below :- This is the correct count as per this Week filter. This is the expected output.

Data Table

Data Table

 

Data Model

Model Connection

 

DateTable 1 - Date Column

Date Column in DataTable1

 

DateTable 2 - Date Column

Date Column in Data Table2

 

Not sure where Im going wrong and whats the issue thats happening. Even my system time shows 9/16/2021 format. Even the model is correct all 

 

New Created Column for Count

Incident New Created Column

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Winniz. This works perfectly fine now 🙂 Cant thank you and the community enough for the help.