Populate a table with Prior Year values only with time intelligence

[ogend]

Hi BI Gurus, 

I have a table :

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 goal is to get to this view  so that Current year is picked from a slicer and prior year is dinamically calculated  as curent year - 1

	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

I was able to get to this by setting up a date table and using the year from the date table in the slicer.

I now need to populate a separate table with all records for Prior Year only (Year selected in the slicer minus 1)

I thought it's be straighforward with SUMMARIZE or CALCULATETABLE and using SAMEPERIODLASTYEAR or PREVIOUSYEAR, but this doesn't seem to work . I also tried this with DATEADD, but no luck either. I am super new to Power BI, could be that I am not using those forumals correctly. Would you be able to help?

Thanks!

Olga

[amitchandak · Accepted solution]

@ogend , I am hoping you have a date column else create a new one like

Date = "01-" & [Period] //Change data type to date.

You can create measure with help from time intelligence

example

Last YTD Sales = CALCULATE(SUM(Table[Revenue]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))

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