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

v-henryk-mstf's avatar
v-henryk-mstf
Icon for Community Support rankCommunity Support
4 years ago

Hi Anonymous ,

 

You can try formula like below:

Col = RANKX('Table','Table'[Date],,DESC,Dense)
M_ =
VAR min_ =
    MIN ( 'Table'[Date] )
VAR max_ =
    CALCULATE ( MAX ( 'Table'[Date] ), 'Table'[Col] = 3 )
RETURN
    DATEDIFF ( min_, max_, DAY )

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


Best Regards,
Henry


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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello thank you for the reply, the last post in this thread has my sample data and and an explanation of what I am trying to do. I can see where RANKX can be useful but I cuold not get the desired result, I am trying to count the number of days in each category, please see my sample data posted earlier today.