Forum Discussion
Comparing two dates from different tables
upload to onedrive and include a link
Hi hakimissimo,
I have fixed your Calc for you and uploaded a new file to the share you created.
Steps:
- Change relationship direction from 'both' to 'single'
- updated the formula to the following, which returns the max('incident-sla'[Start time]) where the 'incident-sla'[Number] = 'Incidents'[Number].
BeforeAck = CALCULATE(max('incident-sla'[Start time]), FILTER('incident-sla', 'incident-sla'[Number] = 'Incidents'[Number]))- Add a Table visual that show how the max value is returned for each rown in the header and detail tables
The related function relates the many side to the side, if you create your column in the incident-sla table your function will work something like
Column = DATEDIFF ( 'Incident-sla'[start time], RELATED ( Incidents[Opened] ), DAY )
It does not really make sense to create the column on the one side on the relationship as you would not know which of the many [start time] values to use for the date diff to the [opened].
If you are trying to use the max or min value from the many side to get the last/first value on the one side, then you could use the lookupvalue function within a calculate function function.
Hope that helps
Hi Richbenmintz,
First thank you for feedback.
What i'm trying to do is basically to compare Start time with Oponed date for each incident (the moment where somebody is working on the resolution).
The tricky point is for a same incident 123456, there could be two start time dates (when ticket is going from support group 1 to support group2...)
Ideally i would like to retrieve the most recent date (because previous dates became obsolete).
I am not familiar with PowerBI and i do not know how to use lookupvalue function.
Hope to read you soon.
++
Hi Richbenmintz,
How can i attach my pbix file to the post?? (very newbie :-)
++
upload to onedrive and include a link