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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous 

 

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
Super User

@Anonymous  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 


Please @mention me in your reply if you want a response.

Copying DAX from this post? Click here for a hack to quickly replace it with your own table names

Has this post solved your problem? Please Accept as 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 C

I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com

Anonymous
Not applicable

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

 

 

Hi @Anonymous 

 

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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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