Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Jackisover
Advocate I
Advocate I

Evolution rate for the current period

Hello,

I have daily datas, since 2022 until this year, a few days ago. 

I would like to be able to display my daily datas, for my current year but also to see the previous year.

 

rapport.png

 

My problem : I would like to calculate an evolution rate, which compares the datas for the current year (blue) with the datas of the same period on the previous year (grey) and not the complete year (red) I can't use "today" fonctions because the datas are not until today. I tried "sameperiodlastyear" but it always gives me the wrong evolution rate. 

 

Of course, when i filter on 2023, I dont have this problem because i have datas for the full year : 

rapport 2023.png

Here is an extract of my datas : 

data extract.png

 

My files here : https://we.tl/t-xDduxHZoYZ

 

I want to automatize this, set it once and for all. 

Can you help me please ? I can't find the solution. 

Thanks !

1 ACCEPTED SOLUTION
MFelix
Super User
Super User

Hi @Jackisover ,

 

If you add a column with the identification of the previous year you can have that value.

 

In the Calendrier table add the following column:

IsPast = 
VAR LastSaleDate = MAX ( 'Data test'[Date] )
VAR LastSaleDatePY = EDATE ( LastSaleDate, -12 )
RETURN
    'calendrier'[Date] <= LastSaleDatePY

Now change your measure to the following code:

Volume N-1 VAR = 

calculate([Volume N],SAMEPERIODLASTYEAR(calendrier[Date]), calendrier[IsPast] = TRUE())

If you use this measure instead of the other on the calculation you will get what is expected:

MFelix_0-1718975159942.pngMFelix_1-1718975170094.png

 

The full explanation is on this blog post.

 

https://www.sqlbi.com/articles/previous-year-up-to-a-certain-date/

 

PBIX File attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



View solution in original post

2 REPLIES 2
MFelix
Super User
Super User

Hi @Jackisover ,

 

If you add a column with the identification of the previous year you can have that value.

 

In the Calendrier table add the following column:

IsPast = 
VAR LastSaleDate = MAX ( 'Data test'[Date] )
VAR LastSaleDatePY = EDATE ( LastSaleDate, -12 )
RETURN
    'calendrier'[Date] <= LastSaleDatePY

Now change your measure to the following code:

Volume N-1 VAR = 

calculate([Volume N],SAMEPERIODLASTYEAR(calendrier[Date]), calendrier[IsPast] = TRUE())

If you use this measure instead of the other on the calculation you will get what is expected:

MFelix_0-1718975159942.pngMFelix_1-1718975170094.png

 

The full explanation is on this blog post.

 

https://www.sqlbi.com/articles/previous-year-up-to-a-certain-date/

 

PBIX File attach.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



This is so great @MFelix, thank you so much for this solution, it was very difficult to me to find it !

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.