previous date

[EDO_01_1789]

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

[Greg_Deckler · Accepted solution]

@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/3395....
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

[EDO_01_1789 · Accepted solution]

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

[EDO_01_1789 · Accepted solution]

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

[v-jianboli-msft · Accepted solution]

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.

[Greg_Deckler · Accepted solution]

@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/3395....
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

[EDO_01_1789 · Accepted solution]

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

[Greg_Deckler]

@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

[EDO_01_1789 · Accepted solution]

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