Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Date difference from Last and 2nd to Last date

Hi, Community.   Good day. I need help to get the date difference of Last and 2nd to the last date. However, the last date should be only when the User Name is Change Analyst - Manila per Status. I...
General Comment
Need Help
Tips and Tricks
Tutorial Requests
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    The date column brought in in the original calculation is wrong. You try this.

    Column:

    Column = 
Var _lastdate = CALCULATE(MAX('User Signoff'[SignOff Date1]),FILTER('User Signoff',[User Name]="Change Analyst - Manila"))
Var _2ndtothelastdate = CALCULATE(MAX('User Signoff'[SignOff Date1]),FILTER('User Signoff',[SignOff Date1]<_lastdate))
Return 
IF([SignOff Date1]=_lastdate,_lastdate-_2ndtothelastdate)

     

    Best Regards,

    Community Support Team _Charlotte

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

     

     

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

Hi, Anonymous 

 

What results you expect from the file you just shared, please identify in the screenshot.

 

Best Regards

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Hi, v-zhangti . Apologies, I am not sure why the formula is not working when I am loading new set of data. The column is converting to date/time, if I convert it to whole number the result will be incorrect. Getting frustrated here. Could you help to check what am I missing? Also is, Manila is blank, then I should use today ()

 

 

Here's the link to the sample data and pbix.

https://drive.google.com/drive/folders/1v9VyVSy0TgzQ-4BKkL3dlnEQmTUOFPzQ?usp=sharing