topic Re: Fetch dates from Another table if it exists between them in Developer

Fetch dates from Another table if it exists between them

12-pune — Sun, 21 Aug 2022 12:03:09 GMT

Hi all, URGENT!!

Please help me on this issue
I have 2 tables: Story data table and Iterations tables.

Story table:

ID	Status	Sprint	BeginDate	End Date
1	Closed	AB		3/16/2022 22:45
2	Closed	CD		3/16/2022 22:45
3	Closed	EF		3/14/2022 11:34
4	Closed	GH		3/15/2022 15:47
5	Closed			3/15/2022 11:25

Iterations Table:

beginDate	endDate
3/16/2022 5:30	3/30/2022 5:30
3/16/2022 5:30	3/30/2022 5:30
3/16/2022 5:30	3/30/2022 5:30
3/16/2022 5:30	3/30/2022 5:30
3/16/2022 5:30	3/29/2022 5:30
3/15/2022 5:30	3/29/2022 5:30
3/15/2022 5:30	3/29/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/16/2022 5:30
3/2/2022 5:30	3/14/2022 5:30

Question: I want to fetch Begin date in STORY TABLE.
Condition: If End Date from Story table comes in between Begindate and End date of Iteration Table, fetch minimum date

I tried with below logics, but its giving me wrong output.
DAX:

IF([Sprint]=BLANK() && [Status]="Closed",

CALCULATE(MIN(Iterations[beginDate]),FILTER(Iterations,Story[EndDate]>=Iterations[beginDate] && Story[EndDate]<=Iterations[endDate])),

Example:
03/16/2022 from story table comes in between of 03/16-03/30, 03/15-03/29 and 03/02-03/16 in Iteration table.

But from above DAX - i am getting output as 03/15, but ideally it should be 03/02 - because 03/16 comes in all 3 range and minimum is 03/02

But output i am getting is 03/15.

Please help community.

Re: Fetch dates from Another table if it exists between them

Fowmy — Sun, 21 Aug 2022 12:23:17 GMT

12-pune

Add the following Calculated Column in the Story Table

BeginDate = VAR __EndDate = Story[End Date] RETURN MINX( FILTER( Iteration, __EndDate>= Iteration[beginDate] && __EndDate <= Iteration[endDate] ), Iteration[beginDate] )

Re: Fetch dates from Another table if it exists between them

12-pune — Sun, 21 Aug 2022 12:48:12 GMT

Fowmy Please help.
Is my filter correct ? I feel there is some issue in filter only. Not sure though.
Thanks for the quick response.
But, It is still showing the same output, 03/15 !!
It should be displayed as 03/02.

Re: Fetch dates from Another table if it exists between them

Fowmy — Sun, 21 Aug 2022 12:51:46 GMT

12-pune

You are getting 03/15 becasue of the time factor. The 16-Mar in Story Table is with the time 10:45 PM. All the end dates between 02-Mar and 16-Mar are not qualified as the time on end date is 5:30 AM, it picks the next minimum 15-Mar

Re: Fetch dates from Another table if it exists between them

12-pune — Sun, 21 Aug 2022 13:11:45 GMT

Fowmy I have removed the time clause from begindate of ITERATION table, then tried with MINX dax which you provided, but still not the expected output.
Still no luck, got the 03/15 only.
😞

Re: Fetch dates from Another table if it exists between them

Fowmy — Sun, 21 Aug 2022 13:35:08 GMT

12-pune

I removed the tiome from both the tables and got the correct result

Check the attached file. Ignore the other tables

Re: Fetch dates from Another table if it exists between them

12-pune — Mon, 22 Aug 2022 07:41:01 GMT

Fowmy

Thanks for quick response, please help to solve this.

Even i have removed the time clause from both tables, dates are not correctly populating.
Still i am getting 03/15, which is not correct.

Please find below screenshot:

Iteration Table:

Story Table:

DAX Used:

AlignBeginDate =

IF(Align_StoriesData[Jira status]="Closed",

MINX(FILTER(Iterations,Align_StoriesData[AlignEndDate]>=Iterations[beginDate] && Align_StoriesData[AlignEndDate]<=Iterations[endDate]),Iterations[beginDate]),

IF(Align_StoriesData[Jira status]<>"Closed" && Align_StoriesData[Sprint]<> BLANK(),LOOKUPVALUE(Iterations[beginDate],Iterations[Iterations],Align_StoriesData[Sprint]),

IF(Align_StoriesData[Sprint]=BLANK() && Align_StoriesData[Jira status]="IceBox",

Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=2)),

IF(Align_StoriesData[Sprint]=BLANK() && Align_StoriesData[Jira status]=BLANK(),

Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=1)),

IF(Align_StoriesData[Sprint]=BLANK() && NOT(Align_StoriesData[Jira status]) IN {"IceBox","Closed","Declined"},

Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=1))

)))))

Re: Fetch dates from Another table if it exists between them

Fowmy — Mon, 22 Aug 2022 08:01:36 GMT

12-pune

Code looks correct. how did you remove the date ? Did you apply formatting or remove in Power Query ?
Can you just paste the sample data in this reply box from Excel, just to verify there is no time components.
If you can paste this data from these two tables in a new Power BI and share with your formula, I can also check on that.

Re: Fetch dates from Another table if it exists between them

12-pune — Mon, 22 Aug 2022 13:28:37 GMT

Fowmy
Yes, i have removed the time constraint from Power Query.
Iteration:

beginDate	endDate
1/5/2022	1/19/2022
1/5/2022	1/19/2022
1/18/2022	2/1/2022
1/19/2022	2/2/2022
1/31/2022	2/14/2022
2/2/2022	2/16/2022
2/2/2022	2/16/2022
2/2/2022	2/16/2022
2/15/2022	3/1/2022
2/16/2022	3/2/2022
2/16/2022	3/2/2022
2/16/2022	3/2/2022
3/2/2022	3/14/2022
3/1/2022	3/15/2022
3/1/2022	3/15/2022
3/1/2022	3/15/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/2/2022	3/16/2022
3/15/2022	3/29/2022
3/15/2022	3/29/2022
3/16/2022	3/29/2022
3/16/2022	3/30/2022
3/16/2022	3/30/2022
3/16/2022	3/30/2022
3/16/2022	3/30/2022
3/29/2022	4/12/2022
3/29/2022	4/12/2022
3/30/2022	4/12/2022
3/30/2022	4/13/2022
3/30/2022	4/13/2022
3/30/2022	4/13/2022
4/13/2022	4/26/2022
4/12/2022	4/26/2022
4/13/2022	4/27/2022
4/13/2022	4/27/2022
4/13/2022	4/27/2022
4/13/2022	4/27/2022
4/13/2022	4/27/2022
4/13/2022	4/27/2022
4/26/2022	5/10/2022
4/26/2022	5/10/2022
4/27/2022	5/10/2022
4/27/2022	5/11/2022
4/27/2022	5/11/2022
4/27/2022	5/11/2022
4/27/2022	5/11/2022
4/27/2022	5/11/2022
4/27/2022	5/11/2022
5/10/2022	5/24/2022
5/11/2022	5/24/2022
5/11/2022	5/24/2022
5/11/2022	5/25/2022
5/11/2022	5/25/2022
5/11/2022	5/25/2022
5/11/2022	5/25/2022
5/11/2022	5/25/2022
5/11/2022	5/25/2022
5/24/2022	6/7/2022
5/24/2022	6/7/2022
5/25/2022	6/7/2022
5/25/2022	6/8/2022
5/25/2022	6/8/2022
5/25/2022	6/8/2022
5/25/2022	6/8/2022
5/25/2022	6/8/2022
5/25/2022	6/8/2022
6/7/2022	6/21/2022
6/8/2022	6/21/2022
6/7/2022	6/21/2022
6/7/2022	6/21/2022
6/8/2022	6/21/2022
6/8/2022	6/22/2022
6/8/2022	6/22/2022
6/8/2022	6/22/2022
6/8/2022	6/22/2022
6/8/2022	6/22/2022
6/8/2022	6/22/2022
6/22/2022	7/5/2022
6/22/2022	7/5/2022
6/22/2022	7/5/2022
6/22/2022	7/5/2022
6/21/2022	7/5/2022
6/21/2022	7/5/2022
6/22/2022	7/6/2022
6/22/2022	7/6/2022
6/22/2022	7/6/2022
6/22/2022	7/6/2022
6/22/2022	7/6/2022
6/22/2022	7/6/2022
7/6/2022	7/19/2022
7/6/2022	7/19/2022
7/5/2022	7/19/2022
7/6/2022	7/19/2022
7/6/2022	7/19/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/6/2022	7/20/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/19/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/2/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
7/20/2022	8/3/2022
8/3/2022	8/16/2022
8/2/2022	8/16/2022
8/3/2022	8/16/2022
8/3/2022	8/16/2022
8/3/2022	8/16/2022
8/3/2022	8/16/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/3/2022	8/17/2022
8/17/2022	8/30/2022
8/17/2022	8/30/2022
8/17/2022	8/30/2022
8/17/2022	8/30/2022
8/16/2022	8/30/2022
8/17/2022	8/30/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/17/2022	8/31/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022
8/31/2022	9/13/2022

Story Table:

id	Sprint	Jira status	AlignBeginDate	AlignEndDate
710	Maliang 22PI2.3	Closed	3/2/2022	3/15/2022
711	Maliang 22PI2.IP	Closed	3/1/2022	3/14/2022
712	MasterPO 22PI2.IP	Closed	3/15/2022	3/16/2022
713	MasterPO 22PI2.IP	Closed	3/15/2022	3/16/2022
714		Closed	3/2/2022	3/15/2022

Begindate and End date in Story table are generated using below DAX logics:

BeginDate:

AlignBeginDate =
IF(Align_StoriesData[Jira status]="Closed",
MINX(FILTER(Iterations,Align_StoriesData[AlignEndDate]>=Iterations[beginDate] && Align_StoriesData[AlignEndDate]<=Iterations[endDate]),Iterations[beginDate]),

IF(Align_StoriesData[Jira status]<>"Closed" && Align_StoriesData[Sprint]<> BLANK(),LOOKUPVALUE(Iterations[beginDate],Iterations[Iterations],Align_StoriesData[Sprint]),

IF(Align_StoriesData[Sprint]=BLANK() && Align_StoriesData[Jira status]="IceBox",
Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=2)),

IF(Align_StoriesData[Sprint]=BLANK() && Align_StoriesData[Jira status]=BLANK(),
Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=1)),

IF(Align_StoriesData[Sprint]=BLANK() && NOT(Align_StoriesData[Jira status]) IN {"IceBox","Closed","Declined"},
Calculate(MAX(JiraSWSprints[start]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=1))
)))))

End Date:
AlignEndDate =
IF(Align_StoriesData[Jira status]="Closed" && Align_StoriesData[Sprint]<> BLANK(),Align_StoriesData[Jira Resolution Date],

IF(Align_StoriesData[Jira status]="Closed" && Align_StoriesData[Sprint]= BLANK(),Align_StoriesData[Jira Resolution Date],

IF(Align_StoriesData[Jira status]<>"Closed" && Align_StoriesData[Sprint]<> BLANK(),LOOKUPVALUE(Iterations[endDate],Iterations[Iterations],Align_StoriesData[Sprint]),

IF(Align_StoriesData[Sprint]=BLANK() && Align_StoriesData[Jira status]="IceBox",
Align_StoriesData[PI EndDate],

IF(Align_StoriesData[Sprint]<>BLANK() && Align_StoriesData[Jira status]="IceBox",
LOOKUPVALUE(Iterations[endDate],Iterations[Iterations],Align_StoriesData[Sprint]),

IF(Align_StoriesData[Sprint]<>BLANK() && NOT(Align_StoriesData[Jira status]) IN {"IceBox","Closed","Declined"},
LOOKUPVALUE(Iterations[endDate],Iterations[Iterations],Align_StoriesData[Sprint]),

IF(Align_StoriesData[Sprint]=BLANK() && NOT(Align_StoriesData[Jira status]) IN {"IceBox","Closed","Declined"},
Calculate(MAX(JiraSWSprints[end]),FILTER(JiraSWSprints,JiraSWSprints[Rank]=1)))))))))

For some dates, we are getting invalid Story Begin Date, which we are discussing on this forum, we need to resolve this issue only.

Thanks for all help.

Re: Fetch dates from Another table if it exists between them

12-pune — Mon, 22 Aug 2022 14:11:41 GMT

Fowmy I am not able to attach Power bi file here, its throwing an error ".pbix files not supported".
Not sure, what is the issue!

Can you share ur email, so that I can send sample data over email.

Re: Fetch dates from Another table if it exists between them

12-pune — Mon, 22 Aug 2022 15:05:40 GMT

Fowmy Issue has been resolved, from another column - i havent removed the time constraint.
Once i removed it, now data is properly displayed.

Thanks for all responses.
See you on other issues 🙂