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

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.

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Thanks, I am testing your measure but not getting the right result. this is a better sample of the actual data. as I add new records I need to compare to the previous date, when I add ID 2, compare to ID 1, when I add ID 3, compare to ID 2. Below sample shows ID 5, I need to compare to ID 4. Using Min and Max return ID 1 and ID 5 which is not what I need to check for name change. Thank for your help

 

 

IDCategoryDateStatusDays
1aName7/27/22  
2aName8/10/22No Change14 days
3aName8/17/22No Change21 Days
4bName8/25/22Change1 Days
5bName9/1/22No Change6 Days
  • Chew_WenJie's avatar
    Chew_WenJie
    Icon for Resolver III rankResolver III
    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.

     



    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you, I will try this on Tuesday when I return to work and let you know.

       

      I really appreciate the help

      • v-henryk-mstf's avatar
        v-henryk-mstf
        Icon for Community Support rankCommunity Support
        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