Forum Discussion

OAlvarezGarcia's avatar
OAlvarezGarcia
New Member
7 years ago

lastest value

 

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?

 

MachineCodeArticle NumberManStartTimeQuTotalOrderedQuTotalQuWasteQuGood
1102344211111403.01.2018 07:37:001100160001600
1252344211111410.01.2018 08:55:00110015921271465
1082344211111410.01.2018 11:22:001000nullnull1405
1392344211111410.01.2018 14:18:001000137111370
1482344211111410.01.2018 16:39:0010001100131087

 

 

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!

 

2 Replies

  • AkhilAshok's avatar
    AkhilAshok
    Solution Sage
    7 years ago

    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] )
  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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