Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.