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



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

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

Fowmy's avatar
Fowmy
Super User
4 years ago

12-pune 

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

 

Check the attached file. Ignore the other tables

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

    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))
    )))))
    • 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.