Show All Months Or Last Month Slicer

[alee5210]

Hi Everyone,

Just having a slight issue. I want to have a month/date slicer that has the options "Select All Months" and "Last Month" in there and I want users to only be able to select one of these options. Right now we have the "Select All" option, but to me it doesn't make the most sense to have it like that.

My data looks like the below. The blanks in the "Data Period" column just mean that the data was from 2 or more months ago.

Data Point	Data Period
a
b
c
d	Last 1 Month/s
e	Last 1 Month/s

Is it possible?

[alee5210]

For anyone who has been looking for a solution, I have found one using a table to join to the one that's in the original post. I created another table below.

Data Period	Slicer Data Period
Last 1 Month/s	Last 1 Month/s
	Select All Months
Last 1 Month/s	Select All Months

I then create a many to many relationship between the 2 columns that are named 'Data Period' and put a slicer on the dashboard using the 'Slicer Data Period' column and ensure that 'Single Selection' is enabled in the slicer.

When I select 'Last 1 Month/s' in the slicer, only the last 1 month of data is shown in the dashboard (data point d and e only). When I select 'Select All Months' in the slicer, it brings in all the data (data point a to e).

[ExcelMonke]

You could still use the Data Period Column to use the "Is last month variable". Set up the formula with a SWITCH statement and if "All Months" is selected to show all values using the ALL function

[alee5210]

Hi @ExcelMonke , I don't quite understand what you mean. I don't have "All Months" in my data, since it's either 'Last 1 Month/s' or blank in the data period column. Could you provide a bit more direction? I wrote the following but doesn't seem to work well for me.

Is Last 3 Months = Var LastM = calculate(all('Test Table'), 'Test Table'[Data Period] = "Last 3 Months")

Return

switch(selectedvalue('Test Table'[Data Period])="Last 3 Months",

isblank(selectedvalue('Test Table'[Data Period]))=TRUE, ALL('Test Table'))

[v-zhangti]

Hi, @alee5210 

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it. If not already resolved, please provide the output you expect based on the example data you provided?

Best Regards,

Community Support Team _Charlotte

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

[ExcelMonke]

Hello,
There are a couple ways to skin this cat. One way to do it (which I have used in my own reports) is to:

1. Create a seperate slicer table with 1 column named "slicer" (or whatever you wish), and two rows. One row with "Last Month" and the other with "All Months"
2. Next, in your main table, create a calculated column with the following DAX: 

Is Last Month =
VAR LastM = MONTH(TODAY())-1

RETURN
IF(MONTH(Table[Date])=LastM,"Last Month",0)

• Create a slicer with the column you created in the new table as the field value (in this case it will be "slicer"). In slicer settings, choose the style as Tile, and only turn on "Multi-Select" in the selection menu. Next, in the "Filters on this Visual" pane, filter out "All months" as an option.
• The end product will leave you with a single button that says "Last Month". If the end user clicks it, it filters to values with only "Last Month". If the tile is unselected, all values appear (i.e., values that are both "All Months" AND "Last Month")

Hope this helps! 

[alee5210]

Hi, I'm still searching for a solution. I would like to have a 2 buttons that the user selects. The 'Select All Months' or 'Last Month' in the slicer. Not just a single value in the slicer. Do you have any work arounds for that?

Also my data has a coloumn which says 'Data Period' because we're not using live data. Sometimes data only comes once a month, sometimes every fortnight. So using the 'Is last month' variable won't work for my case here :S