topic Re: How do I create a DAX calculating the change per sequence? in DAX Commands and Tips

How do I create a DAX calculating the change per sequence?

CJBoelt — Tue, 28 Feb 2023 04:40:44 GMT

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!

Re: How do I create a DAX calculating the change per sequence?

Anonymous — Tue, 28 Feb 2023 05:24:57 GMT

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,
Shreya

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

Re: How do I create a DAX calculating the change per sequence?

CJBoelt — Tue, 28 Feb 2023 18:17:54 GMT

@shreyamukkawar

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

Re: How do I create a DAX calculating the change per sequence?

wdx223_Daniel — Wed, 01 Mar 2023 03:58:22 GMT

=SUM(Table[[Total Actual hours])-CALCULATE(SUM(Table[[Total Actual hours]),TOPN(1,FILTER(ALLSELECTED(Table[SEQ]),Table[SEQ]<MAX(Table[SEQ])),Table[SEQ]))

Re: How do I create a DAX calculating the change per sequence?

CJBoelt — Wed, 01 Mar 2023 17:01:39 GMT

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!

Re: How do I create a DAX calculating the change per sequence?

wdx223_Daniel — Thu, 02 Mar 2023 00:09:23 GMT

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#])))