Forum Discussion
How do I create a DAX calculating the change per sequence?
Here is a Example of what the new graph would be.
Essentially subtracting the actual_hours with all the values in the previous rows. This will provide a measure to show the difference of change for each sequence.
Any suggestions?
Thank you!
=SUM(Table[[Total Actual hours])-CALCULATE(SUM(Table[[Total Actual hours]),TOPN(1,FILTER(ALLSELECTED(Table[SEQ]),Table[SEQ]<MAX(Table[SEQ])),Table[SEQ]))
5 Replies
Hi CJBoelt ,
Please find below solution
Diff = SUM(Sheet1[Total Actual hours])- CALCULATE(SUM(Sheet1[Total Actual hours]),OFFSET(-1,ALLSELECTED(Sheet1[SEQ]),ORDERBY(Sheet1[SEQ])))Best Regards,
ShreyaAppreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!- wdx223_Daniel
Community Champion
=SUM(Table[[Total Actual hours])-CALCULATE(SUM(Table[[Total Actual hours]),TOPN(1,FILTER(ALLSELECTED(Table[SEQ]),Table[SEQ]<MAX(Table[SEQ])),Table[SEQ]))
wdx223_Daniel
This works! Thank you!I have one additional question if you are able,
I have a function [Gaussian_Hours] which calculates a forecast of values which by a normal Distribution, though for some reason this new measure is incredible show (to measure the increase by SEQ.Do you have any ideas for why the performance is slow?
Thank you!
- wdx223_Daniel
performance optimization is hard work, it involves many aspects. you can try this code, maybe it won't work or not.
SeqChange=[Gaussian_Hours]-CALCULATE([Gaussian_Hours],OFFSET(-1,,ORDERBY(PROJ_FRIDAY_SEQ[SEQ#])))
This unfortunately doesn't work for my table, perhaps it is due to the fact that there are many values of SEQ, each corresponding due a different _ID? With your previous code is just repeats actual_hours in Diff.
Apologies, here is a better example.
ID SEQ ACTUAL_HOURS DIFF Job1 1 5 5 Job1 2 10 5 Job1 3 20 10 Job2 1 3 3 Job2 2 10 7 Job2 3 15 5 Job2 4 25 10
