cancel
Showing results for
Did you mean:
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:

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:

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
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.

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.

3 REPLIES 3
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) )

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.

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

www.excelwithallison.com

Helper III

Hello, Thank you!

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

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

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.

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.

Announcements

#### 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.