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
Anonymous
Not applicable

Running total until condition is met, then start running total again

Hi, 

 

Hoping to get some help if possible been hung on a problem for a few days and hoping someone may have done something similar in the past; 

 

@v-yuta-msft I saw you answer a similar question here: https://community.powerbi.com/t5/Desktop/DAX-query-to-add-numbers-until-a-condition-is-met/m-p/69464...

So hoping it may be a similar resolution with the additional condition. 

 

Like in the post above I have two columns calculated by original cost column with an index then previous cost column is index -1 

Then I have a difference column to calculate the difference between the current value and previous value as a % changed value. 

2nd to this there is also a Time column and we need the difference between the time in the 1st time column and 2nd time column. 

 

Then where I am totally stumped is creating a column or measure for the below logic (if it is possible): 

Every time the change is + or - 0.5% (sum) or the time change has been 30 minutes total = 1, then reset and start to sum the time change and value change % again until it is either +/- 0.5% or been another 30 minutes - until the end of the dataset. 

 

So the difference and conditional column would look like the below; 

Price change %   Time Change   Price Cumulative    Time C'    Counter

0.14                     5                      0.14                         5             0

0.27                     10                    0.41                         15           0

0.07                     5                      0.48                         20           0

0.03                     5                      0.51                         25           1             -- start the cumulative sum again

--

0.17                     5                      0.17                         5             0

 

Thanks for any help in advance will be extremely helpful along with putting me out my pain.

 

Thanks

 

 

 

3 REPLIES 3
Stachu
Community Champion
Community Champion

I don't think itt's possible with just the columns you shown - in DAX the order in which the rows are displayed is not relevant at all, you need an actual column that could be referenced to establish the order of events (e.g. date + time, index table, etc.). The Time Change is in minutes, correct?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Anonymous
Not applicable

Morning, 

 

Yeah, there is a Timestamp column and an index column which does accurately order the events. 

 

Thanks, 

 

Josh

Stachu
Community Champion
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1Column2
A1
B2.5

This way I can play with the formulas



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

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.