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
GiuseppeTE's avatar
GiuseppeTE
Frequent Visitor
7 years ago

Hello Nathaniel and thanks for your curiosity. Really I need to solve the issue and maybe I semplified too much the real table I have to manage.

Anyway here below your last formula with the results on my table.

 

 

 

 

So let me tell you that in my table there are 50columns and 330.000 row.

Now the formula I am looking for have  extract the value of actualcycletime corrispondent at the last date available, max shift, with status 200. So on the picture above is 7074110,43.

 

In addition if this can help you, for me is not so necessary to select the status (200), but absolutly I have to select the value of actual_cycle available at the:

  1. lastdate [shift date] and then
  2. at the max value of [shift_no]  that is not always 3.

 

Thanks

Giuseppe

Nathaniel_C's avatar
Nathaniel_C
Community Champion
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