cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

SAME PERIOD LAST YEAR

Hi All,

basic Question from my side.

HOW TO USE SAME PERIOD LAST YEAR  ---- in ----- calculated columns ?

1 ACCEPTED SOLUTION
Community Support

If you are using dates from a fact table, try this way,

``````column =
VAR _date =
EDATE ( 'Table'[Date], -12 )
RETURN
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= _date
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
)
)``````

If you want to create measure, then try this

``LY = CALCULATE(SUM('Table'[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))``

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

11 REPLIES 11
Frequent Visitor

I have another solution for how to calculate last year same-month

Last year revenue = CALCULATE(
SUM( 'table'[revenue]),
FILTER(
'table',
'table'[month/date] = DATE( YEAR(TODAY()) - 1, MONTH(TODAY()), 01)

)
Community Support

If you are using dates from a fact table, try this way,

``````column =
VAR _date =
EDATE ( 'Table'[Date], -12 )
RETURN
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALL ( 'Table' ),
'Table'[Date] >= _date
&& 'Table'[Date] < EARLIER ( 'Table'[Date] )
)
)``````

If you want to create measure, then try this

``LY = CALCULATE(SUM('Table'[Sales]),SAMEPERIODLASTYEAR('Table'[Date]))``

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

Helper II

Is SUM necessary?

This answer looks wrong too, can someone educate me a little here? I was assuming it's SAMEPERIODLASTYEAR.

Community Champion

Please mark this as a solution if it worked for you so that other community members can find the solution quickly. Thanks.

Regards,

TruptiS

Helper III

Then I will hide the previous year column

Community Champion

SAMEPERIODLASTYEAR - Returns a table that contains a column of dates shifted one year back in time from the dates specified in date column.
Thus, you can't use it in the column.

you can achieve this using the below steps:
1- For the current date - create 1 concatenated column of date removing '/'. you can add a custom column which says:
Use CONCAT function : eg - =Concat(Month(Date),Year(Date))
2- For the last year's date - create another concatenated column
eg- Concat(Month(Date),year(Date)-1)
3- Return the values of sales by matching column 2(Concatenation of Last year's date) with Column 1(Concatenation of current date).

If you need the exact DAX, then please share sample data with me.

If this helps, then please mark it as a solution and hit the thumbs up.
Regards,

TruptiS

Anonymous
Not applicable

Perfect

Helper III

Hi,

This is how I got the result for Same Period Last Year using calculated column

Helper III

@v-zhangti  Could you help in this ?

Helper III

Super User

@vijay273162 Well, my answer is not to. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, see if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.