cancel
Showing results for
Did you mean:
Regular Visitor

## Newbie help - percentage change from previous row

Hi, i am a total newbie to powerbi and am wondering if anyone can help.  I have a large dataset with sometimes several records in a second with two corresponding value columns (so Date Time / column A / column B)  .  I would like to calculate a percentage change from previous value - but it has to accommodate for there sometimes being a blank cell so it cannot just blindly look at previous record (sometimes it can be more than one blank cell in a column in a row).

So the excel formula would be (A2-A1)/A1 etc, but in reality it could be (A10-A7)/A7 etc.  Is there a PowerBi formula or some other work around that could achieve the result i need?

4 REPLIES 4
Responsive Resident

HI @MarieW ,

Try the following as a calculated column in DAX

``````VarianceCol =
VAR CurrentRowDate = 'Table'[Date]
VAR EarlierDates =
TREATAS (
FILTER ( ALL ( 'Table'[Date] ), 'Table'[Date] < CurrentRowDate ),
'Table'[Date]
)
VAR MaxDate =
LASTDATE ( EarlierDates )
VAR PreviousNonBlankValue =
MAXX (
TOPN (
1,
FILTER (
'Table',
'Table'[Date] IN EarlierDates
&& NOT ( ISBLANK ( 'Table'[Column1] ) )
),
'Table'[Date], DESC
),
'Table'[Column1]
)
RETURN
DIVIDE ( 'Table'[Column1], PreviousNonBlankValue, BLANK () )``````

Let us know if that works for you

Regular Visitor

Thank you both, i think i need to read up a bit more on what i am doing.  I very much appreciate the query "code" but it gave me an error on not being able to use a MAXX code.  I feel a bit  like i am out of my depth so i need to understand the basics a bit better.

Responsive Resident

Did you use the DAX statement in a calculated column?

It worked for me as a calculated column as shown in the screenshot.

Otherwise, try to share a sample of your PBI data file in here for us to review.

Regards

David

Super User

You will need an index or something that indicates to Power BI which row is "before" another. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Announcements

#### 2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.