Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
Solved! Go to Solution.
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
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
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
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