Forum Discussion

12-pune's avatar
12-pune
Frequent Visitor
4 years ago
Solved

Fetch dates from Another table if it exists between them

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:...
Need Help
  • Fowmy's avatar
    Fowmy
    4 years ago

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



Fowmy's avatar
Fowmy
Super User
4 years ago

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.



12-pune's avatar
12-pune
Frequent Visitor
4 years ago

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.