DAX - Stuck on dynamic MAXX column formula

Question

I'm working in Power BI.

I have a table with member card usage data called NonSameDayUses:

https://www.screencast.com/t/yeSjoqonZ

I have another table with member card add data called AddsOnly:

https://www.screencast.com/t/zlPBRWaDqC

The tables are related by the GUID_TranDate2 field. I am trying to add a column to NonSameDayUses that provides the date just before the use date (to calculate when the amount used was added to their card). I have tried a million things, but this is my current formula and I can't figure out what is wrong with it:

DateAdded = MAXX ( FILTER ( AddsOnly, AND ( AddsOnly[member_guid] = [member_guid], AddsOnly[ValueAddDate] < [TransactionDate] ) ), AddsOnly[TransactionDate] )

Neither filter is working for me. If I try it with just the first argument (member_guid), I get blanks. If I try with the second (dates) I get the max date for the whole table with no filtering.

Any help would be sooooooooooo appreciated, as I am currently banging my head against the wall! :)

DAX0110 DAX0110 · Accepted Answer · 2018-02-01T10:55:16

Try qualifying all the column names, it should work:

DateAdded = 
MAXX( 
    FILTER( 
        AddsOnly
        , AND( 
            AddsOnly[member_guid] 
                = NonSameDayUses[member_guid]
            , AddsOnly[ValueAddDate] 
                < NonSameDayUses[TransactionDate] 
            ) 
        )
    , AddsOnly[TransactionDate] 
)

DAX - Stuck on dynamic MAXX column formula

1 Answers