Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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?
Morning,
Yeah, there is a Timestamp column and an index column which does accurately order the events.
Thanks,
Josh
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).
Column1 | Column2 |
A | 1 |
B | 2.5 |
This way I can play with the formulas
User | Count |
---|---|
128 | |
112 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
116 | |
102 | |
71 | |
57 |