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:

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.

 

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



10 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    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 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.

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