Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am looking for help calculating the difference between the Numbers column below if 'Table'[Book] = EARLIER ('Table'[Book]) . The column below is what I would need. This would help determine if Numbers were skipped if Column => 1
Book | Numbers | Column |
A | 1 | 0 |
A | 2 | 1 |
A | 5 | 3 |
A | 8 | 3 |
A | 9 | 1 |
A | 12 | 3 |
A | 15 | 3 |
A | 16 | 1 |
A | 17 | 1 |
A | 25 | 8 |
Any help is appreciated.
Solved! Go to Solution.
Right, so you would subtract the numbers. The intention was to show you how to use MAXX/MINX coupled with EARLIER and other filters to figure out the previous row value that you want to target and then you can use that to calculate what you want.
Hi,
Try this calculated column formula
=if(ISBLANK(CALCULATE(MAX(Table1[Numbers]),FILTER(Table1,Table1[Book]=EARLIER(Table1[Book])&&Table1[Numbers]<EARLIER(Table1[Numbers])))),BLANK(),[Numbers]-CALCULATE(MAX(Table1[Numbers]),FILTER(Table1,Table1[Book]=EARLIER(Table1[Book])&&Table1[Numbers]<EARLIER(Table1[Numbers]))))
Hope this helps.
Hey thank you for your reply. Is it possible to have a calculated measure for this scenario. As the data i have to deal with contains so many rows and with calculated columns it takes alot of time. And our basic necessity is to reduce the time for the reporting.
Thank you
This is awesome and if you were doing this with dates just substitue with Datediff vs the -.
Thanks again!
See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...
I can't use DATEDIFF because there is no date in this case.
Right, so you would subtract the numbers. The intention was to show you how to use MAXX/MINX coupled with EARLIER and other filters to figure out the previous row value that you want to target and then you can use that to calculate what you want.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |