Calculate automatically the average number of employees between two years

[browniiesx]

Hello everyone!

I'm new to Power BI, and just stumbled to something a little more complicated. Hope you can help!

It's HR data that presents the community on a specific date, and you filter it by year on the page. I got 2019, 2020 and 2021, and each corresponds to data from 2018-12-31, 2019-12-31 and 2020-12-31. I got a table date that looks like this:

DATE TABLE

Date	Period
2018-12-31	2019
2019-12-31	2020
2020-12-31	2021

It's linked to all of my tables by the Period column, that I added manually to all of them. Still, all my data for the 3 years are on the same table, named POPULATION.

Now, I have to calculate the average number of employees between the current and previous year ((4000 in 2019+6000 in 2020)/2= 5000. Simple like that). But I have to do it automatically with each year with their previous year (2021-2020, 2020-2019, 2019-2018, that I will add).

I thought, since there is an overall filter for the year, I could use SAMEPERIODLASTYEAR measure in a CALCULATE measure to filter only the people from previous year. I made different measures, but they give me an error OR give me the current number of people only. Here is the formula I used:

(CALCULATE(COUNTA('POPULATION'[People]), SAMEPERIODLASTYEAR('DATE TABLE'[Date]))+
(COUNTA('POPULATION'[People]))/2

I also used PREVIOUSYEAR, but still not working. 

I also tried to just filter the people from the previous year (CALCULATE(COUNTA('POPULATION'[People]), SAMEPERIODLASTYEAR('DATE TABLE'[Date])), but still not working.

Thank you for all your help!

[amitchandak]

@browniiesx , with help from date table

Rolling 3 = divide( CALCULATE(COUNTA('POPULATION'[People]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), filter(POPULATION,not(isblank((COUNTA('POPULATION'[People])))))))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.