Forum Discussion

GiuseppeTE's avatar
GiuseppeTE
Frequent Visitor
7 years ago
Solved

LASTDATE FILTER

  Hello,   I need on the follow table a formula that will extract just last value. (94.118,74) the filter to apply are: lastdate of  [shift_date] the max of  [shift_no] [status]=200   than...
  • Nathaniel_C's avatar
    Nathaniel_C
    7 years ago

    GiuseppeTE ,

    I am sorry. I thought you wanted a measure that would give you 1 value to use in a visual. Upon rereading this, I see that you are really looking for a column. There may be a better way to do this and I am sure that you can consolidate this, but I have run out of time. So, I created some columns mostly with if statements and added them to the table. It is not pretty, but I believe it works.

    I added 4 columns which as I said you can probably consolidate:

     

     

    selected_last_date = LASTDATE(ALL(Cycles[shift_date]))
     
    selected_last_shift = IF(Cycles[status] =200 && Cycles[shift_date]=Cycles[selected_last_date],(Cycles[shift_no]))
     
    last_actual_cycle_time = (IF(Cycles[status] =200 && Cycles[shift_date]=LASTDATE(ALL(Cycles[shift_date])) && (Cycles[shift_no])= MAX (Cycles[selected_last_shift]), Cycles[actual_cycle_time]))
     
    cycleactuallast = MAX(Cycles[last_actual_cycle_time])
     
    As I said I was working on putting this into one column, but ran out of time, so there may be some duplication, but with these columns it works.
     
    For a single measure that you can display in a card, I have this:
    FINAL = VAR Maxdate = MAX(Cycles[shift_date])
    VAR Maxnumber = CALCULATE(MAX(Cycles[shift_no]),Cycles[shift_date]=Maxdate)
    Var Maxstatus = 200
    return Calculate(MAX(Cycles[actual_cycle_time]),Cycles[shift_date]=Maxdate,Cycles[status]=Maxstatus,Cycles[shift_no]=Maxnumber)
    If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
    Nathaniel
Nathaniel_C's avatar
Nathaniel_C
Icon for Community Champion rankCommunity Champion
7 years ago

Hi GiuseppeTE ,

 

Try this, my table is 'Find Shift'.

Last Shift Date = CALCULATE(Min('Find Shift'[CycleActualLast]),'Find Shift'[Status]=200,'Find Shift'[ShiftNo]=3,LASTDATE('Find Shift'[ShiftDate]))
 
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel
 
GiuseppeTE's avatar
GiuseppeTE
Frequent Visitor
7 years ago

Hello Nathaniel,

 

thanks for your feedback but are not work as I need.

Infact the result is on the last column on the  follow table.

 

I need the last one value by the formula. In the case below 15618,22

 

 

 

 

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion
    7 years ago

    Hi GiuseppeTE ,

    So the last one does not have a status = 200?

    What does your formula return?

    Nathaniel

    • GiuseppeTE's avatar
      GiuseppeTE
      Frequent Visitor
      7 years ago

      hello 

       

      you are right.

      I made a mistake the last one have to be linked with 200.

      The picture I attached is the result of your formula. 

       

      Giuseppe

      • Nathaniel_C's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity Champion
        7 years ago

        Hi GiuseppeTE ,

        So do you think it is working?

        I dummied up a table with the last rows from your picture. However I did notice that of the rows that I used, the last column values were the same, so I put in unique values. The second picture shows the result of 15, which is what I would expect, unless I do not understand your question.

         

        What sort of data is this? I am just curious...

        If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
        Nathaniel