Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
DigiS
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
v-caliao-msft
Employee
Employee

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

View solution in original post

1 REPLY 1
v-caliao-msft
Employee
Employee

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors