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

View solution in original post

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 305 members 3,366 guests
Please welcome our newest community members: