Hope you all are doing alright, I want to return the next date after executing the FIRSTDATE() Dax function. I know there are two functions namely FIRSTDATE and LASTDATE but that does not fulfill my requirement.
Kindly check the sample data below As you can see in the state name column there are 4 states for Under Progress having different dates for each. I want to return the date right after the first date of the state "Under Progress" i.e 8/16/2019. Your help is appreciated thank you.
Actually, I am looking for a calculated column that will help me calculate the date difference in days for each stage. Most applications follows the stages Submitted -> Under Progress -> Pending -> Verified. Some applications may not follow every stage so it can vary (e.g An application may have gone through Submitted and Under Progress stages only).
I'm attaching my calculated columns below the screenshot for your reference.
The issue that I am facing here is that I am getting the negative date difference it's because it is taking the first date of the next stage.
For example, if I want to calculate the date difference for Submitted to Under Progress. In the above screenshot, you can see that it has calculated the negative date difference for the same even though there were two dates for "Under Progress" that were associated with the same application. I want it in such a way that lastvalue should take the second oldest date that'll help me solve the problem.