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