Same Period Last Year

[PatrickLamoste]

Hi,

I am working with a data source set up like this: 

Year	Month	Headcount	Department
2020	Feb	291	Management
2020	Feb	261	Management
2020	Feb	65	Management
2020	Feb	224	Management
2020	Feb	1331	Management
2020	Feb	1548	Management
2020	Feb	656	Management
2020	Feb	424	Management
2020	Feb	676	Management
2020	Feb	947	Management
2020	Jan	291	Management
2020	Jan	258	Management
2020	Jan	64	Management
2020	Jan	228	Management
2020	Jan	1365	Management
2020	Jan	1534	Management
2020	Jan	623	Management
2020	Jan	419	Management
2020	Jan	682	Management
2020	Jan	913	Management
2019	Dec	291	Management
2019	Dec	265	Management
2019	Dec	64	Management
2019	Dec	224	Management
2019	Dec	1366	Management
2019	Dec	1534	Management
2019	Dec	607	Management
2019	Dec	431	Management
2019	Dec	660	Management
2019	Dec	917	Management
2019	Nov	292	Management
2019	Nov	269	Management
2019	Nov	63	Management
2019	Nov	224	Management
2019	Nov	1361	Management
2019	Nov	1538	Management
2019	Nov	600	Management
2019	Nov	431	Management
2019	Nov	658	Management
2019	Nov	916	Management
2019	Oct	295	Management
2019	Oct	266	Management
2019	Oct	62	Management
2019	Oct	221	Management
2019	Oct	1337	Management
2019	Oct	1504	Management
2019	Oct	591	Management
2019	Oct	432	Management
2019	Oct	652	Management
2019	Oct	885	Management
2019	Sep	295	Management
2019	Sep	261	Management
2019	Sep	62	Management
2019	Sep	216	Management
2019	Sep	1365	Management
2019	Sep	1462	Management
2019	Sep	576	Management
2019	Sep	426	Management
2019	Sep	639	Management
2019	Sep	884	Management
2019	Aug	308	Management
2019	Aug	254	Management
2019	Aug	63	Management
2019	Aug	216	Management
2019	Aug	1331	Management
2019	Aug	1437	Management
2019	Aug	561	Management
2019	Aug	428	Management
2019	Aug	634	Management
2019	Aug	884	Management
2019	Jul	304	Management
2019	Jul	242	Management
2019	Jul	59	Management
2019	Jul	207	Management
2019	Jul	1280	Management
2019	Jul	1414	Management
2019	Jul	543	Management
2019	Jul	424	Management
2019	Jul	611	Management
2019	Jul	849	Management
2019	Jun	303	Management
2019	Jun	244	Management
2019	Jun	56	Management
2019	Jun	200	Management
2019	Jun	1257	Management
2019	Jun	1380	Management
2019	Jun	534	Management
2019	Jun	410	Management
2019	Jun	603	Management
2019	Jun	822	Management
2019	May	297	Management
2019	May	247	Management
2019	May	52	Management
2019	May	221	Management
2019	May	1267	Management
2019	May	1376	Management
2019	May	527	Management
2019	May	406	Management
2019	May	598	Management
2019	May	819	Management
2019	Apr	297	Management
2019	Apr	247	Management
2019	Apr	52	Management
2019	Apr	221	Management
2019	Apr	1301	Management
2019	Apr	1300	Management
2019	Apr	516	Management
2019	Apr	398	Management
2019	Apr	592	Management
2019	Apr	809	Management
2019	Mar	296	Management
2019	Mar	275	Management
2019	Mar	52	Management
2019	Mar	218	Management
2019	Mar	1271	Management
2019	Mar	1284	Management
2019	Mar	505	Management
2019	Mar	408	Management
2019	Mar	608	Management
2019	Mar	809	Management
2019	Feb	290	Management
2019	Feb	269	Management
2019	Feb	51	Management
2019	Feb	214	Management
2019	Feb	1261	Management
2019	Feb	1269	Management
2019	Feb	488	Management
2019	Feb	405	Management
2019	Feb	608	Management
2019	Feb	799	Management
2019	Jan	286	Management
2019	Jan	272	Management
2019	Jan	53	Management
2019	Jan	221	Management
2019	Jan	1247	Management
2019	Jan	1249	Management
2019	Jan	459	Management
2019	Jan	392	Management
2019	Jan	560	Management
2019	Jan	747	Management
2020	Mar	291	Management
2020	Mar	260	Management
2020	Mar	66	Management
2020	Mar	230	Management
2020	Mar	1327	Management
2020	Mar	1602	Management
2020	Mar	687	Management
2020	Mar	427	Management
2020	Mar	703	Management
2020	Mar	980	Management

What I need to get is the sum of the headcount for the same period last year. I tired the SAMEPERIODLASTYEAR DAX formula and it didn't work.

Thank you in advance.

[v-kelly-msft · Accepted solution]

Hi @PatrickLamoste,

You need a measure as below:

Measure = CALCULATE(SUM('Table'[Headcount ]),FILTER(ALL('Table'),'Table'[Month]=SELECTEDVALUE('Table'[Month])&&'Table'[Year]=SELECTEDVALUE('Table'[Year])-1))

Finally you will see:

For the related .pbix file,pls click here.

If you wanna use "SAMEPERIODLASTYEAR",you need a calendar table,then get the Month from the date,create a relationship between the 2 tables with column "Month".

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

@PatrickLamoste 

if You have date and date calendar

YTD Sales = CALCULATE(SUM(Table[head count]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Table[head count]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

This year =CALCULATE(SUM(Table[head count]))
trailing year = CALCULATE(SUM(Table[head count]),dateadd('Date'[Date],-1,year))

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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

With a year table, you can also try

This Year= calculated([Measure],filter(All(Year),Year[Year]<=max(Year[Year])))
last Year= calculated([Measure],filter(All(Year),Year[Year]<=max(Year[Year])-1))

You can use Year from this table, but all will remove other filters