cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PatVi
Helper III
Helper III

Formula using the value of previous year

Good afternoon,

I'm struggling with a code. Let me explain what I trying to do.

I got 5 measures and 1 column(Exercice Civil non limité),

I join you a photo of my measures:

PatVi_0-1623229714010.png

All my measures is right exept the first one ("Port Ouverture1").

Indeed I would like to have for "Port Ouverture1" the value of 0 for the oldest year and then the value of "Portefeuille cloture" of the previous year. For exemple in 2016 I would like to have a "port ouverture" of 0 because its the oldest year, then in 2017 a value of 255...

For information, Portefeuille cloture is equal to "Port ouverture1"+ "Production"-"Sans effet"-"Resiliation"

 

My code right now for "Port ouverture1" is:

PatVi_1-1623229903522.png

Exercice Civil non limité is my first columns on the first pick and it contains the years.

Minanne is a column which tells me the minimum years of my data.

I would like to add a line of code on my if statement to say that if its false then it will be equal to "portefeuille cloture" of the previous year and not of this year

 

if someone have some clue to help me it will be very nice!

Thank you!

best regards

Ps: Tell me if it's not clear. 

 

1 ACCEPTED SOLUTION
v-jingzhang
Community Support
Community Support

Hi @PatVi 

 

You could use a Date table which has continuous distinct dates, and create a relationship between your original fact table and Date table on date column. If your fact table has duplicate date values, this relationship should be one-to-many (Date table on one side and fact table on many side). Then you could refer to below articles.

DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question - RADACAD

Time Comparisons In Power BI: This Year vs Last Year | Enterprise DNA

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

 

View solution in original post

3 REPLIES 3
AllisonKennedy
Super User III
Super User III

@PatVi  This could easily be done with a DimDate table and 

 

Port Overture 1 = CALCULATE( [Port Overture] , DATEADD( DimDate[Date], -1, Year) )

 

https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html 

https://excelwithallison.blogspot.com/2020/10/datekey-how-to-relate-your-date-data.html 


Check out my Tokyo 2020 report with live stats - KUDOS much appreciated



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hello, Thank you!

but it's written that every date should be only once

PatVi_0-1623231525046.png

And that's not my case.

Futhermore, what are you calling Port Ouverture in your code? Because you put Port Ouverture and Port Ouverture 1.

Thank you

 

 

v-jingzhang
Community Support
Community Support

Hi @PatVi 

 

You could use a Date table which has continuous distinct dates, and create a relationship between your original fact table and Date table on date column. If your fact table has duplicate date values, this relationship should be one-to-many (Date table on one side and fact table on many side). Then you could refer to below articles.

DateAdd vs ParallelPeriod vs SamePeriodLastYear; DAX Time Intelligence Question - RADACAD

Time Comparisons In Power BI: This Year vs Last Year | Enterprise DNA

 

Regards,
Community Support Team _ Jing
If this post helps, please Accept it as the solution to help other members find it.

 

View solution in original post

Helpful resources

Announcements
MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Top Solution Authors
Top Kudoed Authors