Forum Discussion

PatrickLamoste's avatar
PatrickLamoste
Icon for Helper I rankHelper I
6 years ago
Solved

Same Period Last Year

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 ...
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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