Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Getting previous date by category, not min date

Hello all, this Measure is working as long as there are only 2 dates Min and Max, but I need to get the date before Max regardless if it's Min or not. 1 table, 1 date column and 1 category column. I ...
Need Help
  • Chew_WenJie's avatar
    Chew_WenJie
    4 years ago

    Thanks for clearer explanation, below you can try the new measure for the different of the days compare to previous ID.

    Measure =
    var forid = SELECTEDVALUE('Table'[ID])
    var selectdate = SELECTEDVALUE('Table'[Date])
    var previousdate = CALCULATE(MAX('Table'[Date]),FILTER(ALL('Table'),'Table'[ID]=forid-1))
    var result = DATEDIFF(previousdate,selectdate,DAY)
    return
    IF(previousdate=BLANK(),BLANK(),result)

    Another measure is the status: 
    Status =
    var forid = SELECTEDVALUE('Table'[ID])
    var forcategory = SELECTEDVALUE('Table'[Category])
    var result = CALCULATE(MAX('Table'[Category]),FILTER(ALL('Table'),'Table'[ID]=forid-1))
    return
    IF(result=BLANK(), BLANK(), IF(forcategory<>result, "Change", "No Change"))
     

     


     

    Hope this can help you.

     



  • v-henryk-mstf's avatar
    v-henryk-mstf
    4 years ago

    Hi Anonymous 

     

    If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


    Looking forward to your feedback.


    Best Regards,
    Henry

Chew_WenJie's avatar
Chew_WenJie
Icon for Resolver III rankResolver III
4 years ago

Hi, you may try to use MAXX/MINX instead of MAX/MIN. and if you want to by category, you can add

var category1 = selectedvalue('category')

 

return

CALCULATE(IF(_previousBKT = _currentBKT && _MinDate = _Last,VALUE(UTCNOW()- _MinDate), DATEDIFF(_MinDate,_Last,DAY)), 'category' = 'category1')

 

Hope this can help you 😁

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hello, thanks for the reply. my goal is to return the date before max not min, but I can't use the earlier function in a measure (or can you?), below is my data simplified, in the table I need to compare ID 1 and 4 and get the datediff in days for aName

1aName7/30/22
2aName7/15/22
3aName7/1/22
4aName8/15/22

 thanks for anu help on this

  • Chew_WenJie's avatar
    Chew_WenJie
    Icon for Resolver III rankResolver III
    4 years ago

    Hello, earlier function are normally used in the calculated column instead of measure. You may refer to the power bi that i had attached below, the measure i done is datediff based on the ID of the name. 

     

    Dummy data:


    Sample measure result: 

    Measure:

     

    Measure =
    var aname = SELECTEDVALUE('Table'[Name])
    var findminID = CALCULATE(MIN('Table'[ID]),'Table'[Name]=aname)
    var findmaxID = CALCULATE(MAX('Table'[ID]),'Table'[Name]=aname)
    var find1stdate = CALCULATE(MAX('Table'[Date]),'Table'[ID]=findminID)
    var findlastdate = CALCULATE(MAX('Table'[Date]),'Table'[ID]=findmaxID)

    var datedifferent = DATEDIFF(find1stdate,findlastdate,DAY)

    return
    datedifferent

     

    .

    Hope this can help you 

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello, thanks for the response. in my data Min(ID) will return the first record in the group, this is not the record I want, I am looking for previous date, as I add new records I am checking for change from the most recent.

       

      Thanks

      • Chew_WenJie's avatar
        Chew_WenJie
        Icon for Resolver III rankResolver III
        4 years ago

        Maybe, you can show me what is the result you wanted from this table, i believe that i will have a better understanding on your requirement.