Forum Discussion

tmhalila's avatar
tmhalila
Icon for Resolver II rankResolver II
2 years ago
Solved

Dynamic Slicer to select previous week as default

Hello team, I need your help I have a dataset that tracks daily events with report date and that date is in relationship with the dim_date attached to this post. I want to add a column in the dim_da...
Need Help
Tips and Tricks
  • lbendlin's avatar
    lbendlin
    2 years ago

    That is possible but only if your Calendar table is sourced in Import Mode and is refreshed at least weekly.

     

    Here's the standard pattern (adjust to your needs):

    - create a string representation of your date column, replace the latest date with a string "Latest"
    - sort that new column by the original date column
    - add a slicer or visual/page/report level filter
    - set the filter to "Latest"
    - publish the pbix to the workspace/app

  • tmhalila's avatar
    tmhalila
    2 years ago

    I tried to add this mesure it worked fine!

     

    cx_last_week = 
VAR max_date = MAX(dim_date[cx_end_of_week])
RETURN 
 If(dim_date[cx_end_of_week] = max_date -7, "Last week", dim_date[weekly_start_monday_day_dates])

     Thanks for your time  

lbendlin's avatar
lbendlin
Icon for Super User rankSuper User
2 years ago

That is possible but only if your Calendar table is sourced in Import Mode and is refreshed at least weekly.

 

Here's the standard pattern (adjust to your needs):

- create a string representation of your date column, replace the latest date with a string "Latest"
- sort that new column by the original date column
- add a slicer or visual/page/report level filter
- set the filter to "Latest"
- publish the pbix to the workspace/app

  • tmhalila's avatar
    tmhalila
    Icon for Resolver II rankResolver II
    2 years ago

    Thanks for your idea,

    I have updated the changes with data related to my dataset and from the idea you provided I can get the current week but I want the last week before the current week. I have tried some formulas with errors.

     

    Attached is the Power BI project.

     

    Sample Project  

    • tmhalila's avatar
      tmhalila
      Icon for Resolver II rankResolver II
      2 years ago

      I tried to add this mesure it worked fine!

       

      cx_last_week = 
VAR max_date = MAX(dim_date[cx_end_of_week])
RETURN 
 If(dim_date[cx_end_of_week] = max_date -7, "Last week", dim_date[weekly_start_monday_day_dates])

       Thanks for your time