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
Anonymous
Not applicable

How to use slicer to get a column's pre slicer value...

Hi All,

If I have a slicer that selects a specific year for something  but I want to get the slicer previous year for a column how to do that?

For exmple

 

slicer value - 2020

 

So in a Matrix two columns:

 

Costs for year selected (2020)       Costs for previous year (2019)

1,000                                                              900

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi I did find a solution Friday afternoon. There is a DAX function to get the selector value named SELECTEDVALUE. I have not tried it yet.

 

Best Regards,

-Tony

 

View solution in original post

7 REPLIES 7
V-lianl-msft
Community Support
Community Support

Hi @Anonymous ,

 

First you need to create an unrelated table that contains the years in the data.

Table = SELECTCOLUMNS(Sheet4,"c_date",YEAR(Sheet4[date]))

Then create a measure and apply it to the visual level filter of matrix.

Measure = IF(YEAR(MAX(Sheet4[date]))=SELECTEDVALUE('Table'[c_date])||YEAR(MAX(Sheet4[date]))=SELECTEDVALUE('Table'[c_date])-1,1)

sample.PNG

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

I like your idea but not sure what happens when a new record with a different date than in the date table is entered into the table with the data.

 

Thanks!

Hi @Anonymous ,

 

Is this problem sloved?
If not, please share the sample pbix or sample data to help you better.

 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous
Not applicable

Hi I did find a solution Friday afternoon. There is a DAX function to get the selector value named SELECTEDVALUE. I have not tried it yet.

 

Best Regards,

-Tony

 

amitchandak
Super User
Super User

I typically use datesytd and totalytd with date calendar

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Ashish_Mathur
Super User
Super User

Hi,

Create a Calendar Table with a calculated column to extract the Year.  Build a relationship from the Date column of your Data Table to the Date column of the Calendar Table.  To your visual, drag Date column from the Calendar Table and select 2020.  Write these measures

Total cost = SUM(Data[Cost])

Total cost in previous year = CALCULATE([Total cost],PREVIOUSYEAR(Calendar[Date]))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
edhans
Super User
Super User

You need a date table (see my link below for how to create a dynamic one), and a decent understanding of Time Intelligence. You will use something to the effect of SAMEPERIODLASTYEAR() in your measure to see the 2019 data. See this for an overview of Time Intelligence.

 

Creating a Dynamic Date Table in Power Query



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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.