topic Re: Fetch Relative Date from another table in DAX Commands and Tips

Fetch Relative Date from another table

yogeshk77 — Tue, 14 Feb 2023 04:55:34 GMT

Need help to fetch relevant date field from other table please.

I have below two tables

Release Calendar Table - Showing Date & Release number for some of the dates

Pull Request Table - Showing Code PR ID, Status & Committed Date

Now, I need a DAX experession for a new Calculated Column in Pull Request Table, to show Release Date
And Logic is -
( PR Commit Date > Release Date that belongs to Release number containing either SM RL OR RM RL words AND Merge Status = Succeeded )
AND
( OR Commit Date <= Release Date that belongs to RElease Number containing either SM RL OR RM RL words AND Merge Status = Succeeded )

For e.g. End Result should look something like this in Pull Request Table

Re: Fetch Relative Date from another table

Anonymous — Tue, 14 Feb 2023 05:47:36 GMT

Hi yogeshk77 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create measures.

Measure = var _1=IF(CONTAINSSTRING(MAX('Release Calendar Table'[Release Number]),"SM RL"),1,IF(CONTAINSSTRING(MAX('Release Calendar Table'[Release Number]),"RM RL"),1,BLANK())) RETURN _1

Measure 2 = MINX(FILTER(ALL('Release Calendar Table'),'Release Calendar Table'[Release date]>=MAX('Pull Request Table'[Commit Date])&&[Measure]=1),'Release Calendar Table'[Release date])

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

Re: Fetch Relative Date from another table

wdx223_Daniel — Tue, 14 Feb 2023 06:15:35 GMT

CalculatedColumn=IF(PullRequestTable[Merge Status]="succeeded",MINXX(FILTER(ReleaseCalendarTable,(CONTAINSSTRING(ReleaseCalendarTable[Release Number],"SM RL")||CONTAINSSTRING(ReleaseCalendarTable[Release Number],"RM RL"))&&ReleaseCalendarTable[Release Date]>=PullRequestTable[Commit Date]),ReleaseCalendarTable[Release Date]))

Re: Fetch Relative Date from another table

yogeshk77 — Tue, 14 Feb 2023 09:35:28 GMT

You are awesome ! Thanks much !

Re: Fetch Relative Date from another table

yogeshk77 — Tue, 14 Feb 2023 09:36:39 GMT

Thank you for help, but I needed a Custom Column instead of a measure. Got the solution now. Thank you anyways.