Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 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 and how I have modeled the data :-

 

 

 

But, Im facing issues on 2 things.

 

1. How do we add the Last Week and This Week to the code and make it work ?

2. if I select multiple checkboxes say for eg :- Say I want to compare This Month vs Last Month and select both. It doesn't work. How can we tweak the code to make it work?

 

Any help would be must appreaciated. 

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-kkf-msft's avatar
    v-kkf-msft
    4 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

10 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , please refer my blogs on these topics they have columns and example measures

     

    Power BI — Month on Month with or Without Time Intelligence
    https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
    https://www.youtube.com/watch?v=6LUBbvcxtKA

     

    Power BI — Week on Week and WTD
    https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
    https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Thanks Amit, though the links and videos are surely of great help I still seem to not get around my head for this Week and Last Week Calculations for count of tickets.

     

    In the above case it I tried adding the below code into the Dax but doesnt work. can you see where Im going wrong. I would need this within the Dax itself and dont want to try outside, as it should work as a filter just like for others.

     

    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),"Period","Last Month","Order",5),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_thisquarterstart),"Period","This Quarter","Order",6),
    ADDCOLUMNS(FILTER(_datetable,[Date]>=_lastmonthstart),"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,[Date]=_thisweek+1),"Period","This Week","Order",13),
    ADDCOLUMNS(_datetable,"Period","Custom...","Order",14)
    • v-kkf-msft's avatar
      v-kkf-msft
      Community Support
      4 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.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        This worked perfectly fine for me. Just one query..

         

        In the code you have mentioned (-2) which basically takes from Monday. My data is basically picked up from ServiceNow..so when I select Last week it takes as below.

         

        Last Week :- (30th August 2021 00.00.00 hrs (Monday) to 5th September 2021 23.59.59 (Sunday). So can the above query be modified to achieve this? or is there any other way. The reason I want it is some times there are few tickets that might be missed if its not considered the time so there will be a count mis match.