Forum Discussion

EDO_01_1789's avatar
EDO_01_1789
Helper I
4 years ago
Solved

previous date

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

Help Requested
  • EDO_01_1789's avatar
    EDO_01_1789
    4 years ago

    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's avatar
    EDO_01_1789
    4 years ago

    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's avatar
    v-jianboli-msft
    3 years ago

    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.

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    4 years ago

    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

    • EDO_01_1789's avatar
      EDO_01_1789
      Helper I
      4 years ago

      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's avatar
        Greg_Deckler
        Community Champion
        4 years ago

        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
  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    3 years ago

    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.