Dynamic Current and Prior Year assignment based on slicer Current Year selection

[ogend]

Hi Power BI Gurus, 

This is how a  simplified version  of my table looks like  - it has multiple years of data in the same format, one line per department for a given Period/ Scenario combination. 

Is there a way to dynamically select Current year from a slicer so that if I select a year - it will be treated as Current Year (CY) and CY-1 will become Prior Year (PY)

Period	Scenario	Department	Revenue
Dec-17	Actual	department1	500
Mar-18	Forecast	department1	550
Jun-18	Forecast	department1	600
Sep-18	Forecast	department1	500
Dec-18	Forecast	department1	530
Dec-18	Actual	department1	535
Mar-19	Forecast	department1	580
Jun-19	Forecast	department1	600
Sep-19	Forecast	department1	620
Dec-19	Forecast	department1	590
Dec-19	Actual	department1	600
Mar-20	Forecast	department1	620
Jun-20	Forecast	department1	630
Sep-20	Forecast	department1	620
Dec-20	Forecast	department1	610
Dec-20	Actual	department1	600
Dec-17	Actual	department2	575
Mar-18	Forecast	department2	633
Jun-18	Forecast	department2	690
Sep-18	Forecast	department2	575
Dec-18	Forecast	department2	610
Dec-18	Actual	department2	615
Mar-19	Forecast	department2	667
Jun-19	Forecast	department2	690
Sep-19	Forecast	department2	713
Dec-19	Forecast	department2	679
Dec-19	Actual	department2	690
Mar-20	Forecast	department2	713
Jun-20	Forecast	department2	725
Sep-20	Forecast	department2	713
Dec-20	Forecast	department2	702
Dec-20	Actual	department2	700
Dec-19	Actual	department 3	345
Mar-20	Forecast	department 3	357
Jun-20	Forecast	department 3	362
Sep-20	Forecast	department 3	357
Dec-20	Forecast	department 3	351
Dec-20	Actual	department 3	350

my final goal would be: I select Current Year = 2020

	2019					2020
	PY	Forecast03	Forecast06	Forecast09	Forecast12	CY
department1	600	620	630	620	610	600
department2	690	713	725	713	702	700
department 3	345	356.5	362.25	356.5	350.75	350

If I select Current Year = 2019

	2018					2019
	PY	Forecast03	Forecast06	Forecast09	Forecast12	CY
department1	535	580	600	620	590	600
department2	615	667	690	713	679	690
department 3						345

There might be department 3 scenario when new departments have no prior year activity. 

Thank you! 

Olga

[ogend]

Thanks a lot for the guidance, @amitchandak 

I was able to build what i need based on the article you shared

Olga

[amitchandak · Accepted solution]

@ogend , Create a date table, Have year there, and use that in the slicer. Use time intelligence for this year vs last year

In case you have a month in a given format, not date. Create date: a new column

Date = "01-" & [Period] // change data type to date and use

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA