Forum Discussion

Jackisover's avatar
Jackisover
Icon for Advocate I rankAdvocate I
2 years ago
Solved

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.

 

 

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 : 

Here is an extract of my datas : 

 

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 !

Need Help
  • MFelix's avatar
    MFelix
    2 years ago

    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:

     

    The full explanation is on this blog post.

     

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

     

    PBIX File attach.

2 Replies

  • MFelix's avatar
    MFelix
    Icon for Super User rankSuper User
    2 years ago

    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:

     

    The full explanation is on this blog post.

     

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

     

    PBIX File attach.

    • Jackisover's avatar
      Jackisover
      Icon for Advocate I rankAdvocate I
      2 years ago

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