Data slicing - multiple years

[ATYB9]

Hello,

I am fairly new to Power BI and 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.

See below my data from Excel file with two tabs (seem to be unable to attach the file for some reason):

Power BI report link 

Excel file link 

First tab "Summary details"

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

Second tab: "Draft position"

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

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. 

Currently the Prior Year is showing the same as the Current selected year column which is incorrect. Many thanks in advance for your help!

[ATYB9]

Sorry forgot to thank you for this! It was very much appreciated!

[Anonymous · Accepted solution]

Hi @ATYB9 ,

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