topic Re: previous date in DAX Commands and Tips

previous date

EDO_01_1789 — Tue, 16 Aug 2022 16:42:56 GMT

Hello,

I would like to create a New column that will allow me to have the time of the previous validator.

here is the result I would like to have.

in my table there are between 5 and 4 validators (column with number 2) by Excel file (column with number 1).
What I want is that in the line of the validator 2, I can have the time (column with number 3) and the date of validation of the validator 1 of the same excel file).

I don't know if it's ideal to do it in M or in DAX ?

for your information:

would it be possible not to modify the formatting (no unpivot or transpose transformation).
validator 1 has no pre-date since it is the creator.

I hope that my request has been well explained and that you could help me please

thanks

Re: previous date

Greg_Deckler — Tue, 16 Aug 2022 16:48:24 GMT

EDO_01_1789 Basically MTBF. 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/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous

Re: previous date

EDO_01_1789 — Tue, 16 Aug 2022 17:05:13 GMT

hello Greg_Deckler

thanks for your quick feedback.

I read your article, but I think that unless I'm mistaken it doesn't apply in my dataset.

If I'm not mistaken in your article you have two different columns for the dates, while in my case I have only one.

what I try to do is only to transcribe the time of validation of the previous step :

for validator 2, show me the time of validator 1
for validator 3, show me the time of validator 2
for validator 4, show me the time of validator 3
for validator 5, show me the time of validator 4

Re: previous date

Greg_Deckler — Tue, 16 Aug 2022 17:56:38 GMT

EDO_01_1789 Well, you would just stop at getting the __PreviousDate. All comes down to the filters you use, so something like:

Column = VAR __Date = [Validator Date] VAR __FileLeafRef = [FileLeafRef] VAR __PreviousDate = SWITCH('Table'[Attribute], "Validator 2 Comments",MAXX(FILTER('Table',[Date]<__Date && [Attribute] = "Validator 1 comments" && [File Leaf Ref] = __FileLeafRef, "Validator 3 Comments",MAXX(FILTER('Table',[Date]<__Date && [Attribute] = "Validator 2 comments" && [File Leaf Ref] = __FileLeafRef, "Validator 4 Comments",MAXX(FILTER('Table',[Date]<__Date && [Attribute] = "Validator 3 comments" && [File Leaf Ref] = __FileLeafRef, BLANK() ) RETURN __PreviousDate

Re: previous date

EDO_01_1789 — Tue, 16 Aug 2022 19:17:08 GMT

Greg_Deckler ,

I feel embarrassed, sorry for the bother.

but what do you want me to add in this part of your formula.

I have only one column that contains dates in my data table, called "Validator Date". I think you have already integrated it in the first variable of your formula (Var_date)

Can you help me?

moreover when i try to change this part by my famous column, i have this error message and i don't know why (is it missing a parenthesis or a comma somewhere)?

thanks for your help.

Re: previous date

v-jianboli-msft — Wed, 31 Aug 2022 08:59:26 GMT

Hi EDO_01_1789 ,

Based on your description, I have created a simple sample:

Please try:

previous date = VAR _num = VALUE ( MID ( [Attribute], 11, FIND ( " :", [Attribute] ) - 11 ) ) VAR _attribute = "Validator " & _num - 1 & " : comments" RETURN MAXX ( FILTER ( 'Table', [FileLeafRef] = EARLIER ( 'Table'[FileLeafRef] ) && [Attribute] = _attribute ), [Validator Date] )

Final output:

Best Regards,

Jianbo Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.