cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GiuseppeTE Frequent Visitor
Frequent Visitor

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:

  1. lastdate of  [shift_date]
  2. the max of  [shift_no]
  3. [status]=200

 

thanks in advance for your help

 

lastdate.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Nathaniel_C Super Contributor
Super Contributor

Re: LASTDATE FILTER

@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:

 

Cycle 1.PNG

 

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)
Cycle 2.PNG
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

7 REPLIES 7
Nathaniel_C Super Contributor
Super Contributor

Re: LASTDATE FILTER

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
 
Find shift.PNG
Highlighted
GiuseppeTE Frequent Visitor
Frequent Visitor

Re: LASTDATE FILTER

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

 

formula dax.JPG

 

lastdate.JPG

 

 

Nathaniel_C Super Contributor
Super Contributor

Re: LASTDATE FILTER

Hi @GiuseppeTE ,

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

What does your formula return?

Nathaniel

GiuseppeTE Frequent Visitor
Frequent Visitor

Re: LASTDATE FILTER

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 Super Contributor
Super Contributor

Re: LASTDATE FILTER

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

 

 

 

Cycle Actual Last1.PNG

 

Cycle Actual Last.PNG

 

GiuseppeTE Frequent Visitor
Frequent Visitor

Re: LASTDATE FILTER

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.

 

formula dax.JPG

 

 

lastdate.JPG

 

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 Super Contributor
Super Contributor

Re: LASTDATE FILTER

@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:

 

Cycle 1.PNG

 

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)
Cycle 2.PNG
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 558 members 4,354 guests
Please welcome our newest community members: