Forum Discussion
Display Data for two years based on Year filter
Hi Guys,
I have this visual, and I want to display selected year and selected year - 1 data only.
Selected Year = academic_year selected from the Filter Pane. I'm not using slicers because those dropdown will be outside of BI Report.
Hello again lovishsood1 ,
I appreciate your follow-up.
Since you’re working exclusively with the Year column and don’t have a full date table, there’s no need for time-intelligence functions. You can achieve your goal efficiently with a straightforward DAX filter, like this:ShowTwoYearsData =
VAR SelectedYear = SELECTEDVALUE('AcademicData'[Year])
RETURN
CALCULATE(
[YourMetric],
FILTER(
'AcademicData',
'AcademicData'[Year] IN { SelectedYear, SelectedYear - 1 }
)
)This measure will accurately return results for the selected year and the previous year. You can use it directly in your visual, and it will function correctly even without a date table.
If you decide to expand to months or quarters in the future, implementing a proper Date table will be beneficial. For now, this year-based approach is the most effective solution for your requirements.
Thak you.
9 Replies
You can adapt the technique described in https://www.sqlbi.com/articles/show-previous-6-months-of-data-from-single-slicer-selection/ to show 2 years instead of 6 months.
Goal: Show data for Selected Year and Selected Year - 1.
DAX Measure:
ShowTwoYearsData =
VAR Selected = SELECTEDVALUE(SelectedYearTable[Year])
RETURN
CALCULATE(
[YourMetric],
FILTER(
'AcademicData',
'AcademicData'[Year] IN {Selected, Selected - 1}
)
)
Use this measure in your visual to filter the data dynamically. Let me know if you want to plug in a specific metric or refine it for stacked visuals.Hi lovishsood1 ,
Thank you Shahid12523 for the response provided!
Has your issue been resolved? If the response provided by the community member addressed your query, could you please confirm? It helps us ensure that the solutions provided are effective and beneficial for everyone.
Thank you for your understanding!Hi lovishsood1 ,
I wanted to follow up and see if you had a chance to review the information shared. If you have any further questions or need additional assistance, feel free to reach out.
Thank you.
Tejaswi.Hi, No its not resolved because I don't have date table in my model and only YEAR COLUMN.
