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 

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 

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