Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have spent hours on this forum and the internet attempting to find a solution to no avail. I beg of your assistance.
I have a measure called "NetRev" which I am attempting to have some conditional running totals on. I wish to have a rolling sum that is reset every time a new negative trend occurs.
I was able to create a running total measure called "BaseDDA2" which has a formula:
If I could use a table with the earlier function, this would be a breeze, but it needs to stay dynamic due to end user constraints (sans data refresh)
If I generate a date when NetRev goes positive, and I try to use it as a date filter condition, is it still a placeholder error? I am guessing this is going wrong because the DDA is doing exactly as expected: pulling a sum of all prior NetRev<0. I need to somehow trigger a reset.
Any help would be greatly appriciated!
Thank you for your efforts @some_bih ! I too have been unable to find a direct solution, but a very effective workaroud came to me today during a walk. It invovles:
As I mentioned, this is not a true solution, but a elegant work-around. I will be running many scenarios, but it's not an unmanageable number, nor a unwieldly sized dataset. If done properly, I can get the behavior I am looking for with only two clicks.
I appriciate you @some_bih ! Thanks again!
Hi @cjmpetroleum I really tried for hours to get you desirable output, but I could find correct approach.
I tried for various combination on row level to create measure or to use IF formulas, but as shown on picture below I did not get correct output. I even tried to implement previous row data but as there are so many combination like from positive number to negative or two days are negative I could not find pattern to create IF statement.
Wish you more luck with this.
Proud to be a Super User!
Hi @cjmpetroleum if you want negative part [M_Amount] replace with 0 in
Or provide Expected columns data.
PS I am writing this via mobile
Proud to be a Super User!
Thank you @some_bih
I have created a sample pbix with some additional explaination within it. I have attempted to load it with the formulas you suggested, but I cannot run a SUM on the target measure.
Many thanks for your help!
https://drive.google.com/file/d/1OPBzJwMVHqhPIcTUyeou7-4GBj8aoytW/view?usp=sharing
Hi @cjmpetroleum I create simple excel data from your picture and in Excel create "test amount".
In PowerBI I created Date Table and relationship with Year column (I import Excel as Date table in format Date dd.mm.yyyy) and following measures. Assumption for Reset Measure is that when negative amount is the year, I hope this is ok for you.
Excel "test data"
PowerBI
Proud to be a Super User!
Hello @some_bih
I appriciate this information and the effort, but my "sum" column is not values, it is a measure. Thus M_Amount = sum(Sheet2[Amount]) will not work as Sheet2[Amount] is a measure (in my case) and not a column. The sum function can only work on columns. If I could somehow convert my measure to a column (without addcolumn, as this does not appear to work properly), then I believe your solution would work.
Thank you for your efforts, but sadly for me, this solution did not work as-is.