Search code examples
reportpowerbidaxpowerquerypowerbi-desktop

power BI Taking MIN and Max date by comparing two tables

I have three table namely Allitems, InItems and OutItems. The main table is Allitems which is related to the other two table with IN_OUT_ID. I have been able to calculate the min and max date based on the IDs for each of the

Dataset

enter image description here

table using the below DAX Formula

For InItems tables

Min_Start_Date = CALCULATE( MIN(InItems[InDate]), ALLEXCEPT( InItems, InItems[IN_ID] ))
Max_end_Date =  CALCULATE( MAX(InItems[InDate]), ALLEXCEPT( InItems, InItems[IN_ID] ))

For OutItems table

Min_Start_Date = CALCULATE( MIN(OutItems[OutDate]), ALLEXCEPT( OutItems, OutItems[OUT_ID]))
Max_end_Date = CALCULATE( MAX(OutItems[OutDate]), ALLEXCEPT( OutItems, OutItems[OUT_ID] ))

the relationship i have change the direction to flow both ways

enter image description here

I am trying to bringback min and max date by comparing the the min and max date from OutItems and Allitems and use it as a column in Allitems table. For example taking IN_ID 1, the Min date is 08/01/2019. I will also take the min date of OUT_ID which is 03/02/2019. Then I want the min date between these two date which is 08/01/2019. Can anybody help on how I can achieve this

Expected outcome

enter image description here

I am open to any question thanks

Solution

  • To do this, you don't need relationships to filter both ways and you don't need those four calculated columns. Just take the min/max of the min/max:

    Mindate =
MIN (
    CALCULATE ( MIN ( InItems[InDate] ) ),
    CALCULATE ( MIN ( OutItems[OutDate] ) )
)

    and

    Maxdate =
MAX (
    CALCULATE ( MAX ( InItems[InDate] ) ),
    CALCULATE ( MAX ( OutItems[OutDate] ) )
)

    Note that the CALCULATE here performs a context transition that applies the row context (the IN_OUT_ID and Item values in the current row) as a filter context when taking the min/max over the other tables. If you remove it, you'd get the min/max across all ID values.