power BI Taking MIN and Max date by comparing two tables

Question

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

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

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

I am open to any question thanks

Alexis Olson Alexis Olson · Accepted Answer · 2019-07-08T16:49:25

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.

power BI Taking MIN and Max date by comparing two tables

1 Answers