cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MarieW
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
Geradav
Responsive Resident
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 () )

 

 
 

Annotation 2020-04-27 161858.png

 

Let us know if that works for you

 

 

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

Geradav
Responsive Resident
Responsive Resident

@MarieW 

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

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

 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

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.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors