Data slicing - multiple years

Question

Hello,&nbsp;I am fairly new to Power BI and&nbsp;I am trying to use slicer to apply in a dynamic way so that the visuals on the same page show the relevant data for the particular year.&nbsp;See below my data from Excel file with two tabs (seem to be unable to attach the file for some reason):&nbsp;Power BI report link&nbsp;Excel file link&nbsp;&nbsp;First tab "Summary details"IDDraft/FinalEntity NameCurrency of dataPeriod EndEntity ABC 31/12/2021DraftEntity ABCGBP test 202131/12/2021Entity ABC 31/12/2020DraftEntity ABCGBP test 202031/12/2020Entity ABC 31/12/2019DraftEntity ABCGBP test 201931/12/2019Entity DEF 30/06/2021DraftEntity DEFUSD test 202130/06/2021Entity DEF 30/06/2020DraftEntity DEFUSD test 202030/06/2020Entity DEF 30/06/2019DraftEntity DEFUSD test 201930/06/2019&nbsp;Second tab: "Draft position"IDHeading 1Heading 2Heading 3Heading 4Heading 5Entity ABC 31/12/202199,999100,122100,245100,368100,491Entity ABC 31/12/202098,99999,12299,24599,36899,491Entity ABC 31/12/201997,99998,12298,24598,36898,491Entity DEF 30/06/2021-2,0004,000-6,000-8,00010,000Entity DEF 30/06/2020-1,0002,000-3,000-4,0005,000Entity DEF 30/06/2019-5001,000-1,500-2,0002,500&nbsp;&nbsp;&nbsp;As you can see, the visuals in the "Current selected year" column is working correctly however I am struggling to find the correct formula for the values in the "Prior year" column (circled in screenshot below in red) to produce the correct results. The prior year results should always be the current selected year minus 1 year. For example if the current selected year in the "Period End" slicer is 2021, then the Prior Year data should show for 2020 for the specific entity selected.&nbsp;&nbsp;Currently the Prior Year is showing the same as the Current selected year column which is incorrect. Many thanks in advance for your help!&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;ATYB9&nbsp;,
I updated your sample pbix file, please find the detailed steps in the attachment.
1. Unpivot Heading1~Heading5 columns of the query 'Merge1'

2. Create a period dimension table(Don't create any relationship with Merge1 table)

3. Update the formula of measure [SelectedYear] as below
SelectedYear = SELECTEDVALUE('Period Ends'[Period End])
4. Create two measures to get the selected year and previous year heading values
CY_Headings =
CALCULATE (
    SUM ( 'Merge1'[Value] ),
    FILTER ( 'Merge1', 'Merge1'[Period End] = [SelectedYear] )
)PY_Headings = 
CALCULATE (
    SUM ( 'Merge1'[Value] ),
    FILTER ( 'Merge1', 'Merge1'[Period End] = [PriorYearToSelectedYear] )
)

PS: After you open the attachment, please update the data source file directory with yours for the query "Summary details" and "Draft position"...

Best Regards

Forum Discussion

Data slicing - multiple years

2 Replies

Related content

Multiple Years Filter

Or Slicing between multiple tables

Multiple measures in matrix by year

Slicing Between Multiple Dates

Daily Average Over Multiple Years

ID	Draft/Final	Entity Name	Currency of data	Period End
Entity ABC 31/12/2021	Draft	Entity ABC	GBP test 2021	31/12/2021
Entity ABC 31/12/2020	Draft	Entity ABC	GBP test 2020	31/12/2020
Entity ABC 31/12/2019	Draft	Entity ABC	GBP test 2019	31/12/2019
Entity DEF 30/06/2021	Draft	Entity DEF	USD test 2021	30/06/2021
Entity DEF 30/06/2020	Draft	Entity DEF	USD test 2020	30/06/2020
Entity DEF 30/06/2019	Draft	Entity DEF	USD test 2019	30/06/2019

ID	Heading 1	Heading 2	Heading 3	Heading 4	Heading 5
Entity ABC 31/12/2021	99,999	100,122	100,245	100,368	100,491
Entity ABC 31/12/2020	98,999	99,122	99,245	99,368	99,491
Entity ABC 31/12/2019	97,999	98,122	98,245	98,368	98,491
Entity DEF 30/06/2021	-2,000	4,000	-6,000	-8,000	10,000
Entity DEF 30/06/2020	-1,000	2,000	-3,000	-4,000	5,000
Entity DEF 30/06/2019	-500	1,000	-1,500	-2,000	2,500