cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
MiKeZZa Member
Member

Slicer with 2 options; show only last month or everything

Hi all,

 

I'm a little lost; I want to present my users a slicer where they'll 2 options: show everything or only show last month. But what I've been trying now is resulting in calculated columns based on my datedim. Then they are like this:

 

Show everything or last month = if(date[Is last month]="Yes";"Show only last month";"Show everything")

 

But what happens is complete right but not what I want: if people choose "Show everything" you see everything except the last month. I think the solution will be easy, but for now I don't see the right solution.... Anyone who can help?

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Slicer with 2 options; show only last month or everything

Hi @MiKeZZa 

One way or another, you will need another table to to allow selection of different periods that overlap.

 

Possible methods:

  1. You could follow an approach similar to this post from Chris Webb.
    • In this post a Period table is created that maps multiple "Periods" to sets of Dates.
    • The Date column of the Period table then has a 1:many bidirectional relationship with the 'Date'[Date] column.
  2. Since you only have two Periods filters of interest, you could use a similar but possibly simpler approach making use of the Is Last Month column you have already defined, and a many:many relationship with a Period table. See sample PBIX here.
    • The logic is similar to Chris Webb's post, but the Period table only needs to map Periods to values of the Is Last Month column, so it can look like this:image.png
    • Then create a many:many relationship between Period[Is Last Month] and 'Date'[Is Last Month] with filter direction set so that Period filters 'Date'.
    • If you wanted to avoid many:many relationships, you could create an Is Last Month table related to 'Date', then have a 1:many relationship between Is Last Month and Period.

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




2 REPLIES 2
Super User
Super User

Re: Slicer with 2 options; show only last month or everything

Hi @MiKeZZa 

One way or another, you will need another table to to allow selection of different periods that overlap.

 

Possible methods:

  1. You could follow an approach similar to this post from Chris Webb.
    • In this post a Period table is created that maps multiple "Periods" to sets of Dates.
    • The Date column of the Period table then has a 1:many bidirectional relationship with the 'Date'[Date] column.
  2. Since you only have two Periods filters of interest, you could use a similar but possibly simpler approach making use of the Is Last Month column you have already defined, and a many:many relationship with a Period table. See sample PBIX here.
    • The logic is similar to Chris Webb's post, but the Period table only needs to map Periods to values of the Is Last Month column, so it can look like this:image.png
    • Then create a many:many relationship between Period[Is Last Month] and 'Date'[Is Last Month] with filter direction set so that Period filters 'Date'.
    • If you wanted to avoid many:many relationships, you could create an Is Last Month table related to 'Date', then have a 1:many relationship between Is Last Month and Period.

Regards,

Owen



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




MiKeZZa Member
Member

Re: Slicer with 2 options; show only last month or everything

Thank you. So easy, so simple. But I had a lockdown of my brain Smiley Happy