How to get Latest Input from previous Output

DAX DAX Calculations
1

Dear Sir/ Madam,

I am trying to get the previous output for my latest input for each step.

image

As you can see from the table above, for step no 80, the input needs to be 90 as the output from step 70 is 90.
Is there a DAX formula that can do this

Sample Data for Input and Output.pbix (51.5 KB)

Sample Operation Overview.xlsx (12.2 KB)
?

Thanks.

2

Hi @Khaiboon

Check if below Calculation works for your case.

Total Input New = var PrevStep = maxx(filter(ALLSELECTED(operationsheet),operationsheet[step no] < max(operationsheet[step no])),operationsheet[step no])

return 
CALCULATE([Total Output],filter(ALLSELECTED(operationsheet),operationsheet[step no] = PrevStep))

Blockquote

Thanks
Ankit J

3

Hi Ankit,

Thanks! It works great!

image

Can I ask if the initial step (step 10) to have the initial input as well?
Cos right now its blank due to the DAX getting previous step and step 10 has no previous step.

Thanks again!

4

Hi @Khaiboon

Have you tried DataMentor to help you will your problem? DataMentor/EDNA AI tools is built within the EDNA Learning platform.

Give it a try.

Thanks
Keith

5

Hi Keith,

Oh no I have not. OK, I’ll give it a try!
Thanks

6

i like DataMentor :slight_smile:

7

Hi @Khaiboon - You can use below.

Total Input New1 = var PrevStep = maxx(filter(ALLSELECTED(operationsheet),operationsheet[step no] < max(operationsheet[step no])),operationsheet[step no])

var Res = CALCULATE([Total Output],filter(ALLSELECTED(operationsheet),operationsheet[step no] = PrevStep))

return

if(ISBLANK(res),[Total Input],Res)

Blockquote

Thanks
Ankit J

8

Thanks Ankit!! Works great!!

1 Like
9

hello

another solution is in using the window function and it is simple DAX
kind regards
Roger

image

10

Hi Roger,
Thanks so much! This works great too, just awesome!

Can I also ask one more question?
What if I want to calculate the latest output and divide it over the first input, so in this case 70/100, what would be the DAX for it?
Thanks again.

11

When can this post be approved?
people ar waiting for this solution for more than 2 days
?

Roger

12

hallo
attached the pbix with the new measure using variables.
you can wrap the two measures ( colored in red ) in a new measure assigning each to a variable and use the function divide() to get the result.
If this is what you are looking for you can delete the two measures TOTAL LATEST INPUT WF
and
TOTAL LATEST OUTPUT WF,
if you need them in a visual then don’t delete.
Attached also a link to a video on how to use the window function.

kind regards

Roger

image
image
Sample Data for Input and Output version 2.pbix (53.2 KB)

Bing Video’s

13

Thanks so much Roger!
Window function is new to me.
I’ll take a look at the tutorial.

Thanks again for the help.