Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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)
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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.
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
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/
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.
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
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingUser | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |