Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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?
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
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.
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
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |