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

 

 

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!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.