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.
Hi,
I am tring to get the last 3 years in my report. it look like i have in my report years 2020 , 2019, 2018, 2017,2016,2015
i wnat to show onaly 2020,2019,2018
also if the year change to 2021
it should update to
2021,2020,2019 and hide 2018, 2017,2016,2015
thank you.
Solved! Go to Solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@Anonymous , if the year is coming from date. Use relative date slice
https://docs.microsoft.com/en-us/power-bi/visuals/desktop-slicer-filter-date-range
Hi @Anonymous ,
You need to filter data in Power Query.
As @AllisonKennedy mentioned, you can use built in functions to calculate last N months, years, days.
But if you want to make sure it calculates years, where last 3 years are 2018, 2019, 2020 no matter which current month it is, here is approach:
1) create new column for year based on data in table: Date.Year([Date])
2) create new column for current year: Date.Year(DateTime.Date(DateTime.LocalNow()))
3) create new column to calculate difference between current year and year in data: [Current Year]-[Year]
4) filter this colum to show only number 0,1,2 (it will filter only last 3 years)
Cheers,
Nemanja
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
I have similar problem but i'm not able to decide how to put your solution into it.
I just want that when i click at the slicer of year (same as the one in the question) I should see the graphs as it is but along with that in the other visual i want to see only the past 3 years of sales/profit in number.
Can anyone please guide me. I did for the current year and last year by using the SAMEASLASTYEAR formula, visualised on the card. However, i tried alot of things but failed to do for last 2 years or mainly N-1, N-2 and N-3 year
Thank you so much in advance !
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
18 |