Forum Discussion
Date difference from Last and 2nd to Last date
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.
Hi, v-zhangti. I am trying to create a DAX formula for getting the date difference between the last date and 2nd to the last date. But the last date should be based on a specific user (i.e. Manila). I have to calculate the days per change number and per status.
As you can see in the sample image, Test1 in Collaborate status, the last date is 12/7/2021, but since I need to consider the user Change Analyst - Manila, the expected last date should be 12/2/2021, and get the date difference to 11/25/2021, which is the 2nd to the last date considering the Change Analyst - Manila as last date. So I need to filter the change number, status, and user name.
Community SupportHi, Anonymous
You can try the following methods.
last date =
CALCULATE(MAX('Table'[Signoff Date]),FILTER(ALL('Table'),[User Name]="Change Analyst - Manila"))2nd to the last date =
CALCULATE(MAX('Table'[Signoff Date]),FILTER(ALL('Table'),[Signoff Date]<[last date]))Expected output = DATEDIFF([2nd to the last date],[last date],DAY)
Is this the result you want? If not, please provide a more detailed description of the results.
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.
Hello again, v-zhangti . This will work only for measures? I tried to add column using this one, but it won't work. Or only measure?
- v-zhangti
Hi, Anonymous
You can try this method.
Column:
Column = Var _lastdate = CALCULATE(MAX('Table'[Signoff Date]),FILTER('Table',[User Name]="Change Analyst - Manila")) Var _2ndtothelastdate = CALCULATE(MAX('Table'[Signoff Date]),FILTER('Table',[Signoff Date]<_lastdate)) Return IF([Signoff Date]=_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 quickly.
- Ashish_Mathur
Super User
Share the download link of your PBI file.
Hi! Apologies, I am very new to Power BI. Can you share how can I attach the pbix here?
- v-zhangti
Hi, Anonymous
If there are still questions, please reply to the post and attach a screenshot of your latest results.
Best Regards
I tried your formula, however, I am not sure why I am getting "1" as a result.
