cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
DigiS Frequent Visitor
Frequent Visitor

formula and slicer to compose different data selections

Question:

I want the slicer (which shows a button for each year) to show the last 13 months when no year is selected. And I want it to show all (and only) the transactions in a specific year when that year is selected. (What I will explain in the background information below does what I discribed before this point, but does not do what I will discribe now) And I want the slicer to show a button for each year from 2014 and upwards. (Now it only shows 2016 and 2017, but only with the data from the last 13 months. While I want the slicer to show all data from 2016 or 2015 or 2014 when I click on this button in the slicer.) I struggled for a long time and I could not find the answer.

 

Background information:

I am building a dashboard, based on a sheet with info like transactiondate, product, product type, type of customer, e.g.

I am using a slicer (that uses a table column which holds the data of the transaction year: the slicer shows a button for each year in the last 13 months, that should become each year in the dataset) and a page level formula (Last_13_M, see below for formula).

Am using the following formulas (which I think are relevant for this question).

 

Last_13_M =
IF(Dim_Kalender[Date] > [Datum (last-13M)];"Last 13M";BLANK())

 

Datum (last-13M) =
//last date in dataset
EDATE(LASTDATE(ALL(Fact_Transactions[TransactionDate]));-13)


       

Hopefully some of you guys can help me with this, because I could not make it work myself. You would really help me with this.

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-caliao-msft
Moderator

Re: formula and slicer to compose different data selections

Hi @DigiS,

 

You can use two slicers to achieve this requirement, and broke the interactions between them.

Create two column in your date table.
Last13Month = IF(DATEDIFF(Sales[Date],TODAY(),MONTH)>12,"History Data","Last 13 Month")
Year = YEAR(Sales[Date])

Ceate two slicers visuals, add those two column into them. And broke the interactions between them.
Select one slicer>Forma>Edit Interactions>None
Capture.PNG

Please note that two slicer work as AND opeartor to filter data, before select items in slicer, please uncheck all items in other slicer.

 

Regards,

Charlie Liao

1 REPLY 1
Moderator v-caliao-msft
Moderator

Re: formula and slicer to compose different data selections

Hi @DigiS,

 

You can use two slicers to achieve this requirement, and broke the interactions between them.

Create two column in your date table.
Last13Month = IF(DATEDIFF(Sales[Date],TODAY(),MONTH)>12,"History Data","Last 13 Month")
Year = YEAR(Sales[Date])

Ceate two slicers visuals, add those two column into them. And broke the interactions between them.
Select one slicer>Forma>Edit Interactions>None
Capture.PNG

Please note that two slicer work as AND opeartor to filter data, before select items in slicer, please uncheck all items in other slicer.

 

Regards,

Charlie Liao