Help with Dax and searching last value

[Antonio_Sanchez]

Hello to all from mexico.

I have a question trying to find some help about DAX, for a particular condition i need to figure out.

i have a table where i get data for last year, where is a measure that get an account for a particular day (like odometer in a car).

i need to get the previus last value about the present value (selected value), to get the average of mile between the day of the last value and the present value.

Date Report	Measure	Selected value date	Last Value Date?	Last Value measure?
01/14/19	2192437	01/14/19
4/4/19	2258293	4/4/19	01/14/19	219437
7/8/19	2343344	7/8/19	4/4/19	225893
9/12/19	2359027	9/12/19	7/8/19	234344
10/7/19	2364782	10/7/19	9/12/19	2359027
12/29/19	2409328	12/29/19	10/7/19	2364782

let me do an example to explaint this

if a want the average of 9/12/19, i need to get the previus value from 7/8/19 and measure 2343344 to get the difference of days (9/12/19 -7/8/19) = 66 days, then get the rest of miles (2359027- 2343344)=15683. and finally divide 15683/66 to get an average per day of 238.

those maths are easy, but because i have several values in a year, i need to get the previus value accordin the selected value to do the maths.

How can i get the previus value according the selected value?

thanks for your help.

regards

[Greg_Deckler · Accepted solution]

See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

[Antonio_Sanchez]

I just can say "Thank you, thank you Greg"

its awesome the knowledge and participation of all the comunity, i really appreciate you attention and help with this,

and curiusly, the post you share its a great guide for persons performing maintenance.

the only consideration  need to share with you, i dont have the second column to calculate measures, i mean, in your example, i dont have the RepairStarted column, only have the RepairCompleted column. that was the reason to try to find a way to get the last data value of the same column for RepairCompleted, to calculate the differences in days between the current value date and the last data value date of the same machine (in your example).

i tried to figure out with the example, and the "VAR next", was a great help, because i can get the next data value for the selected value 

Step 1 =

VAR next = MINX(FILTER(MTBF1,

MTBF1[MachineName] = EARLIEST(MTBF1[MachineName])

&&

MTBF1[RepairStarted] > EARLIER(MTBF1[RepairStarted])

&&

MTBF1[RepairType] <> "PM"

),MTBF1[RepairStarted])

return next

unfortunely not the last date, to know when was tue last date the machine failed, and get the measure of something, like "Tortillas" if you consider what your machine made.

again, Thanks

regards.

[Antonio_Sanchez]

Just a good new, I find the answer to find the last value instead the next value.

i was checking your post several times, and  finding the MAX of the RepairStarted column after filtering our Repair table for machines that are equal to the current machine in the row as well as having a RepairStarted that is before the current.

Step =

VAR next = MAXX(FILTER(MTBF1,

MTBF1[MachineName] = EARLIER(MTBF1[MachineName])

&&

MTBF1[RepairStarted] < EARLIER(MTBF1[RepairStarted])

&&

MTBF1[RepairType] <> "PM"

),MTBF1[RepairStarted])

return next

is totaly appreciate your atention and guide to help understanding DAX.

thanks