Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends September 15. Request your voucher.

Reply
12-pune
Frequent Visitor

Fetch dates from Another table if it exists between them

‎08-21-2022 05:03 AM

Hi all, URGENT!!

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

Story table:

IDStatusSprintBeginDateEnd Date
1ClosedAB 3/16/2022 22:45
2ClosedCD 3/16/2022 22:45
3ClosedEF 3/14/2022 11:34
4ClosedGH 3/15/2022 15:47
5Closed  3/15/2022 11:25

 

Iterations Table:

beginDateendDate
3/16/2022 5:303/30/2022 5:30
3/16/2022 5:303/30/2022 5:30
3/16/2022 5:303/30/2022 5:30
3/16/2022 5:303/30/2022 5:30
3/16/2022 5:303/29/2022 5:30
3/15/2022 5:303/29/2022 5:30
3/15/2022 5:303/29/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/16/2022 5:30
3/2/2022 5:303/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.

 

Labels:
Message 1 of 11
844 Views
0
1 ACCEPTED SOLUTION
Fowmy
Super User
Super User

‎08-21-2022 05:23 AM

@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]
    )



Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

View solution in original post

Message 2 of 11
827 Views
0
10 REPLIES 10
Fowmy
Super User
Super User

‎08-21-2022 05:51 AM

@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 

Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Message 4 of 11
818 Views
0
12-pune
Frequent Visitor
In response to Fowmy

‎08-21-2022 06:11 AM

@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.
😞

Message 5 of 11
811 Views
0
Fowmy
Super User
Super User
In response to 12-pune

‎08-21-2022 06:35 AM

@12-pune 

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

Fowmy_0-1661088815074.png

 

Fowmy_1-1661088848840.png

Check the attached file. Ignore the other tables

Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

21082022.pbix
Message 6 of 11
769 Views
0
12-pune
Frequent Visitor
In response to Fowmy

‎08-22-2022 12:38 AM

@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:

12pune_0-1661153483136.png

Story Table:

12pune_1-1661153743935.png


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))
)))))
Message 7 of 11
737 Views
0
Fowmy
Super User
Super User
In response to 12-pune

‎08-22-2022 01:01 AM

@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.



Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Message 8 of 11
733 Views
0
12-pune
Frequent Visitor
In response to Fowmy

‎08-22-2022 07:11 AM

@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.

Message 10 of 11
720 Views
0
12-pune
Frequent Visitor
In response to 12-pune

‎08-22-2022 08:05 AM

@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 🙂

Message 11 of 11
710 Views
0
12-pune
Frequent Visitor
In response to Fowmy

‎08-22-2022 06:27 AM

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

beginDateendDate
1/5/20221/19/2022
1/5/20221/19/2022
1/18/20222/1/2022
1/19/20222/2/2022
1/31/20222/14/2022
2/2/20222/16/2022
2/2/20222/16/2022
2/2/20222/16/2022
2/15/20223/1/2022
2/16/20223/2/2022
2/16/20223/2/2022
2/16/20223/2/2022
3/2/20223/14/2022
3/1/20223/15/2022
3/1/20223/15/2022
3/1/20223/15/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/2/20223/16/2022
3/15/20223/29/2022
3/15/20223/29/2022
3/16/20223/29/2022
3/16/20223/30/2022
3/16/20223/30/2022
3/16/20223/30/2022
3/16/20223/30/2022
3/29/20224/12/2022
3/29/20224/12/2022
3/30/20224/12/2022
3/30/20224/13/2022
3/30/20224/13/2022
3/30/20224/13/2022
4/13/20224/26/2022
4/12/20224/26/2022
4/13/20224/27/2022
4/13/20224/27/2022
4/13/20224/27/2022
4/13/20224/27/2022
4/13/20224/27/2022
4/13/20224/27/2022
4/26/20225/10/2022
4/26/20225/10/2022
4/27/20225/10/2022
4/27/20225/11/2022
4/27/20225/11/2022
4/27/20225/11/2022
4/27/20225/11/2022
4/27/20225/11/2022
4/27/20225/11/2022
5/10/20225/24/2022
5/11/20225/24/2022
5/11/20225/24/2022
5/11/20225/25/2022
5/11/20225/25/2022
5/11/20225/25/2022
5/11/20225/25/2022
5/11/20225/25/2022
5/11/20225/25/2022
5/24/20226/7/2022
5/24/20226/7/2022
5/25/20226/7/2022
5/25/20226/8/2022
5/25/20226/8/2022
5/25/20226/8/2022
5/25/20226/8/2022
5/25/20226/8/2022
5/25/20226/8/2022
6/7/20226/21/2022
6/8/20226/21/2022
6/7/20226/21/2022
6/7/20226/21/2022
6/8/20226/21/2022
6/8/20226/22/2022
6/8/20226/22/2022
6/8/20226/22/2022
6/8/20226/22/2022
6/8/20226/22/2022
6/8/20226/22/2022
6/22/20227/5/2022
6/22/20227/5/2022
6/22/20227/5/2022
6/22/20227/5/2022
6/21/20227/5/2022
6/21/20227/5/2022
6/22/20227/6/2022
6/22/20227/6/2022
6/22/20227/6/2022
6/22/20227/6/2022
6/22/20227/6/2022
6/22/20227/6/2022
7/6/20227/19/2022
7/6/20227/19/2022
7/5/20227/19/2022
7/6/20227/19/2022
7/6/20227/19/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/6/20227/20/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/19/20228/2/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/20/20228/2/2022
7/20/20228/3/2022
7/20/20228/3/2022
7/20/20228/3/2022
7/20/20228/3/2022
7/20/20228/3/2022
7/20/20228/3/2022
7/20/20228/3/2022
8/3/20228/16/2022
8/2/20228/16/2022
8/3/20228/16/2022
8/3/20228/16/2022
8/3/20228/16/2022
8/3/20228/16/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/3/20228/17/2022
8/17/20228/30/2022
8/17/20228/30/2022
8/17/20228/30/2022
8/17/20228/30/2022
8/16/20228/30/2022
8/17/20228/30/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/17/20228/31/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022
8/31/20229/13/2022


Story Table:

idSprintJira statusAlignBeginDateAlignEndDate
710Maliang 22PI2.3Closed3/2/20223/15/2022
711Maliang 22PI2.IPClosed3/1/20223/14/2022
712MasterPO 22PI2.IPClosed3/15/20223/16/2022
713MasterPO 22PI2.IPClosed3/15/20223/16/2022
714 Closed3/2/20223/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.

Message 9 of 11
706 Views
0
12-pune
Frequent Visitor

‎08-21-2022 05:39 AM

@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.

Message 3 of 11
823 Views
0
Fowmy
Super User
Super User

‎08-21-2022 05:23 AM

@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]
    )



Did I answer your question? Mark my post as a solution! and hit thumbs up


⭕ Subscribe and learn Power BI from these videos

⚪ Website ⚪ LinkedIn ⚪ PBI User Group

Message 2 of 11
828 Views
0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.

View All