Min Date from three unrelated tables

[Tmk123]

Hello,

I am creating a calculated column and it's returning blanks.  Is there a good way to manage this?  Perhaps the statement under the "Return" can be altered?  The three tables are not related.  Thank you.

_Create Date =

VAR _itemNo = Table1[ItemNo]

VAR _Date_InvMaster =

        CALCULATE( MIN(Table1[record_est_date]), FILTER(Table1, Table1[item_num] = _itemNo))

VAR _Date_HockPO =

        CALCULATE( MIN('Table2'[received_date]), FILTER('Table2', 'Table2'[item_num] = _itemNo))

VAR _Date_Inventory =

        CALCULATE( MIN(Table3[LastReceipt]), FILTER(Table3, Table3[ItemNo] = _itemNo))

RETURN

MIN(MIN(_Date_InvMaster, _Date_HockPO), _Date_InvMaster)

Below is the result.  The item number is on the left.  The three numbered columns show the VAR outputs above.  The last column is the output of the calculated column.  My goal is to have the minimum date from the three VARs appear in the calculated column.

[mark_endicott · Accepted solution]

@Tmk123 You need to exclude blanks from the calculations, otherwise MIN will find that. You can also improve the RETURN statement to use MINX which will iterate over a VAR that unions all the results together, you can try amending the DAX below to suit your model:

VAR _itemNo = 'Table'[item_num]
VAR _Date_InvMaster =
    CALCULATETABLE (
        SUMMARIZE (
            'Table',
            'Table'[item_num],
            "Date", MIN ( 'Table'[record_est_date] )
        ),
        'Table'[item_num] = _itemNo,
        'Table'[record_est_date] <> BLANK ()
    )
VAR _Date_HockPO =
    CALCULATETABLE (
        SUMMARIZE ( Table2, Table2[item_num], "Date", MIN ( Table2[received_date] ) ),
        'Table2'[item_num] = _itemNo,
        Table2[received_date] <> BLANK ()
    )
VAR _Date_Inventory =
    CALCULATETABLE (
        SUMMARIZE ( Table3, Table3[item_num], "Date", MIN ( Table3[LastReceipt] ) ),
        Table3[item_num] = _itemNo,
        Table3[LastReceipt] <> BLANK ()
    )
VAR _union_tables =
    UNION ( _Date_InvMaster, _Date_HockPO, _Date_Inventory )
RETURN
    MINX ( _union_tables, [Date] )

[mark_endicott]

@Tmk123 - Have you been able to check if this solution works fr you?

[Tmk123]

Thanks for your response.  I am in the middle of trying your suggestion.  It's giving me a circular dependency error so I'm researching to see how to overcome that - that is, unless you see why it's giving me that error.

[mark_endicott]

@Tmk123 - I cant see your error without seeing the code you have written.

Besides I have tested the code on a replication of your data and it works, see below:

I have also attached my pbix file so you can see that I have structured it exactly as you have set out from your description. The only thing I have changed is the item numbers. 

[Tmk123]

Thank you!!

[Gabry]

Hello,

I think one problem is that you called 2 times the same variable date_invmaster.

MIN(MIN(_Date_InvMaster, _Date_HockPO), _Date_InvMaster)

The second problem I think it's that you are not excluding blank values. So the min is always the blank.

You need to exclude blanks

[Tmk123]

So right! I did call it twice.  Thank you!