Dax to lookup from multiple values same table

[Brianlewis0927]

Hello, 

I'm trying to find a way to create a DAX forumula that will tell me if a customer did business with us in the previous year or not. My idea is to look at customer "A", and search the entire column 'Year' to find "2016". I can do this in excel, but having some trouble putting this in DAX. 

Thanks everyone

[v-jiascu-msft]

Hi @Brianlewis0927,

You could try this formula as a calculated column.

IfPrevious =
IF (
    CALCULATE (
        COUNTROWS ( 'Table1' ),
        FILTER (
            'Table1',
            (
                'Table1'[Year]
                    = EARLIER ( Table1[Year] ) + 1
                    || 'Table1'[Year]
                        = EARLIER ( Table1[Year] ) - 1
            )
                && 'Table1'[Customer] = EARLIER ( Table1[Customer] )
        )
    )
        > 0,
    "Yes",
    "No"
)

Best Regards!

Dale

[marcorusso · Accepted solution]

Just a small note: I see many people still using EARLIER in Power BI, but by using variables the code is much easier to read. I no longer use EARLIER if I can use variables (not available in Excel 2010/2013 and SSAS 2012/2014). I just converted the code, even if I'm wonder why testing year with both +1 and -1... I would have used only -1...

IfPrevious =
VAR CurrentYear = Table1[Year]
VAR CurrentCustomer = Table1[Customer]
RETURN
    IF (
        CALCULATE (
            COUNTROWS ( 'Table1' ),
            FILTER (
                'Table1',
                ( 'Table1'[Year] = CurrentYear + 1
                    || 'Table1'[Year] = CurrentYear - 1 )
                    && 'Table1'[Customer] = CurrentCustomer
            )
        )
            > 0,
        "Yes",
        "No"
    )

 Marco Russo - SQLBI

[v-jiascu-msft]

@marcorusso

Hi Marco,

It's such a big honor to me to get guidance from you. I will try it from now on. It's easy to understand the code by using variables. About why testing year with both +1 and -1, I was going to get the result showed from the picture of OP. Usually, testing with one part is enough.

Thank you and best regards!

Dale

[marcorusso]

@v-jiascu-msftyou're welcome, and thanks for using www.daxformatter.com, the code is much easier to read (for me)! 🙂

[Brianlewis0927]

Hey Dale, 

Thanks for the help.  I haven't heard of "earlier()", reading now on what it does exactly. 

That seemed to solve 1 out of 2 problems for me. 

I like the idea of going by year, but what if I wanted to go based off a word and not year? seems like this formula is requiring it to be a number. 

Example, instead of using 2017 or 2016. I have a dynamic column that updates daily using the word "trailing 12 months" and "last year trailing 12 months" 

[v-jiascu-msft]

Hi @Brianlewis0927,

We still can add a new column, which will be numeric. Then we can use the formula. 

NewColumn =
SWITCH (
    [Dynamic],
    "trailing 12 months", 2017,
    "last year trailing 12 months", 2016,
    0
)

If you have many other words, you can append them to "switch". 

If the answer works, please mark it as solution. Thank you in advance.

Best Regards!

Dale