lastest value

[OAlvarezGarcia]

Hello,

I am trying to get the output of units from the last machine in this case the formula will say for article 23442111114 total output (QuGood) 1087 units. The total output needs to be calculated with the last ManStartTime since this is the last machine that this product has gone through.

Could you please recomend me a formula that will work with this information?

MachineCode	Article Number	ManStartTime	QuTotalOrdered	QuTotal	QuWaste	QuGood
110	23442111114	03.01.2018 07:37:00	1100	1600	0	1600
125	23442111114	10.01.2018 08:55:00	1100	1592	127	1465
108	23442111114	10.01.2018 11:22:00	1000	null	null	1405
139	23442111114	10.01.2018 14:18:00	1000	1371	1	1370
148	23442111114	10.01.2018 16:39:00	1000	1100	13	1087

Another question that I have is it possible to recalculate the values as you can see the machine 110 has reported a QuGood 1600 but the next machine has only recived 1592,  the different should be waste for the first machine 110. Is is possible to recalculate this information?

Thank you!

[v-lili6-msft]

hi, @OAlvarezGarcia

AkhilAshok had provided a measure that to calculate lastest value for each Article Number,

and if you want to  recalculate the value of QuWaste,

you may try to this way:

Step1:

Add a column  group date rank for each Article Number,

group rank = RANKX(FILTER('Table','Table'[Article Number]=EARLIER('Table'[Article Number])),'Table'[ManStartTime],,ASC)

Step2:

Add a column that next time QuTotal

next time Qutotal = CALCULATE(SUM('Table'[QuTotal]),FILTER('Table','Table'[group rank]=EARLIER('Table'[group rank])+1))

then calculate new QuWaste according to your logic like below:

new QuWaste = IF(ISBLANK('Table'[next time Qutotal]),'Table'[QuWaste],'Table'[QuTotal]-'Table'[next time Qutotal])

here is pbix, please try it.

https://www.dropbox.com/s/5wuexrvqcterh60/lastest%20value.pbix?dl=0

If it is not your case, please share sample pbix file or some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Best Regards,

Lin

[AkhilAshok]

How about this:

Latest TS = MAX('Table'[ManStartTime])

Latest QuGood = 
VAR RankedMarchines =
    ADDCOLUMNS (
        SUMMARIZE (
            'Table',
            'Table'[MachineCode],
            'Table'[Article Number],
            'Table'[QuGood]
        ),
        "Rnk", RANKX (
            ALL ( 'Table'[MachineCode], 'Table'[QuGood] ),
            [Latest TS],
            ,
            DESC
        )
    )
VAR FirstRankMarchine =
    FILTER ( RankedMarchines, [Rnk] = 1 )
RETURN
    MAXX ( FirstRankMarchine, 'Table'[QuGood] )