Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
vr83
Frequent Visitor

How to compare the same period of two different year

Hi everyone,

I would like to compare the same period of the current year and the prior year (example YTD march 23 - march 22).

Can I use the function PREVIOUSYEAR?

And how ?

Thanks in advanced.

6 REPLIES 6
BeaBF
Impactful Individual
Impactful Individual

@vr83 if the answer is correct, I ask you to accept it as a solution, otherwise I am available for further clarification.

 

BBF

BeaBF
Impactful Individual
Impactful Individual

@vr83 Hi!

 

Yes, you can use the PREVIOUSYEAR function in DAX to compare the same period of the current year and the prior year. Here is an example formula that you can use to achieve this:

YTD Prior Year =
CALCULATE(
[Total Sales],
PREVIOUSYEAR(Dates[Date]) <= Dates[Date] &&
YEAR(PREVIOUSYEAR(Dates[Date])) = YEAR(MAX(Dates[Date]))
)

 

The calculation is based on the following conditions:

  • The date in the previous year must be less than or equal to the current date.
  • The year of the previous year date must be the same as the year of the latest date in the Dates table.

You will need to replace [Total Sales] with your own measure and Dates[Date] with your own date column.

 

BBF

vr83
Frequent Visitor

Hi BeaBF,

 

homestly I don't know why it doesn't work.

Probably because my record are maybe different.

vr83_0-1680086833612.png

 

So in this case how can I write the correct formula using what you've write?

YTD Prior Year =
CALCULATE(
[Total Sales],
PREVIOUSYEAR(Dates[Date]) <= Dates[Date] &&
YEAR(PREVIOUSYEAR(Dates[Date])) = YEAR(MAX(Dates[Date]))
)

Thansk 

BeaBF
Impactful Individual
Impactful Individual

@vr83  Ok and do you have another column with the YTD? or FY is your YTD?

BeaBF
Impactful Individual
Impactful Individual

@vr83 Firstly, you have to create a column with only the Year and one with only the Month:

YEAR = LEFT(Table[MESI], 4)
MONTH = RIGHT(Table[MESI], 2)
then, you can create this measure:
Delta_YTD = 
VAR CurrentMonth = SELECTEDVALUE(Table[MONTH])
VAR PrevMonth = CurrentMonth - 1
VAR CurrentYear = SELECTEDVALUE(Table[YEAR])
VAR PrevYear = CurrentYear - 1
RETURN
CALCULATE([YTD],
FILTER(ALL(Table), Table[Month]= CurrentMonth &&
Table[Year]= CurrentYear)) - 
CALCULATE([YTD],
FILTER(ALL(Table), Table[Month]= CurrentMonth &&
Table[Year]= PrevYear))
where YTD is your YTD Value.
BBF
vr83
Frequent Visitor

Hi,

I've tried the formula but it doesn't work...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.