Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi every one,
i have a table. i want to show data like "Order date (Years)", "Sales". i have given one slicer for years, when i select year from slicer. i want to display last 5 years slaes.
How can i achive this.... Please help on this...
Thanks in advance.....
Solved! Go to Solution.
Hi @venug20,
Based on my test, you could refer to below steps:
Create a new table:
Table = DISTINCT('Calendar'[Year])
Create a measure:
Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))
Use the new table's [Year] column as slicer and you could get the correct result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi @venug20,
Based on my test, you could refer to below steps:
Create a new table:
Table = DISTINCT('Calendar'[Year])
Create a measure:
Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))
Use the new table's [Year] column as slicer and you could get the correct result:
You could also download the pbix file to have a view.
Regards,
Daniel He
Hi Danhe,
I have almost similar requirement but its based on the date. Say I select todays date 22 Feb 2023 in slicer then I should get last 5 years data (including quarters,months,dates) based on selected date.
This was a life-saver for me! Thanks for the solution.
Hi Daniel,
This works fine for me but I have the year slicer that controls all the visuals on the page. So if I use the Table[year] column as my slicer, then the other visuals remain unchanged giving the total for all the years. Please suggest how can I have the Year slicer from the Calendar table which controls all the other visuals for the selected year at the same time displaying Sales for the last 4 years.
Hi @Anjali_Prakash
I had a similar issue and fixed it by removing the filter (REMOVEFILTERS) on the main calendar table. Using the sample code in this thread it would look like this:
Measure = CALCULATE(SUM(Orders[Sales]),REMOVEFITERS('Calendar'[Year]),(FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))
Hope this helps!
Hi
I could not get the results like i want to.
i need to display the current year selected and previous year by only selecting on one year.
Which means, i need the data of 2016 and 2017 to be displayed when i select on 2017. Ive tried using this formula below but its not working. Is there any other way? My column consumes of formula which leads to another formula but at the end i would only need to use the total column data to be selected based on year.
Measure = CALCULATE(SUM(Orders[Sales]),FILTER('Calendar','Calendar'[Year]<=SELECTEDVALUE('Table'[Year])&&'Calendar'[Year]>=SELECTEDVALUE('Table'[Year])-4))
Hi,
See if my solution here helps - Flex a Pivot Table to show data for x months ended a certain user defined month.
Thanks for your solution, great work. which i has been expected, got the same solution from you.
Hi,
You may please follow these guidelines
Total sales = SUM(Data[Sales])
Total sales for last 5 years = CALCULATE([Total sales],DATESBETWEEN(Calendar[Date],EDATE(MIN(Calendar[Date]),-4),MAX(Calendar[Date])))
Hope this helps.
Thank you for your effort.
i have done which is giving steps in above. i got output like below.....
If not happend selection from slicer. output come like this...
when i select "2017" from slicer. the output should come like "2017, 2016, 2015, 2014, 2013" with respective years sales values.
should not show total sales values to respective years..
Hi,
A simple solution is to use a relative date filter. You may download my PBI file from here.
Hope this helps.
There is no "Year"(Slicer) selection option. If possible, Please help on this issue...
Thanks in advance...
Hi,
In the PBI file that i shared with you, i have already shown the working of the relative date filter in the in the Visual filter section of the FIlter pane.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |