DAX to lookup the value in same column to get the result

[ritu24raj]

Hi

I am struggling to write a DAX code, which involves the list of purely new accounts for my organization.

For Eg - The below table has all the customers till date respective to the year-quarter, now the result accounts are the accounts I need which means that Cvent and Dell are purely new accounts and the others like FB, Google and Lenovo were already became customers so I don't need those.

Account Names	Closed Year-Quarter	Result Accounts
Intuit	2014-Q1
SBI	2014-Q1
Chevron	2016-Q1
Google	2016-Q4
Lenovo	2017-Q1
Oracle	2017-Q2
FB	2017-Q4
HP	2019-Q3
Infosys	2019-Q4
Cvent	2020-Q2	Cvent
FB	2020-Q2
Dell	2020-Q2	Dell
Google	2020-Q2
Lenovo	2020-Q2

Is there a way out to write a DAX for this, please help

[Icey]

Hi @ritu24raj ,

Based on the data in your original post, I created a Measure, please check:

Result Accounts = 
VAR MaxClosedYearQuarter =
    MAXX ( ALLSELECTED ( 'Table' ), 'Table'[Closed Year-Quarter] )
VAR Count_ =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        ALLEXCEPT ( 'Table', 'Table'[Account Names] )
    )
RETURN
    IF (
        Count_ = 1
            && MAX ( 'Table'[Closed Year-Quarter] ) = MaxClosedYearQuarter,
        MAX ( 'Table'[Account Names] )
    )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[parry2k]

@ritu24raj so you want to look at existing record before the Quarter you are viewing the data and anything new in this quarter, you want to show those accounts, correct? How does your raw data look like? I'm sure it has a date component which is important.

[ritu24raj]

@parry2k it does have a date field which is the close date, but I want to write the DAX based on the closed year-quarter and not date. Also, the raw data looks pretty much the same which is mentioned.

Note - I am getting the data using SSAS via live connection.

[ritu24raj]

@amitchandak

@Greg_Deckler

@az38

@Ashish_Mathur 

@Mariusz  - Can you please help me in this

[az38]

@ritu24raj 

why does [Result Accounts] is empty for row 

Lenovo

2017-Q1

?

[ritu24raj]

@az38 - If you see my original post, I do not need the accounts which are duplicated, I just need the accounts which are PURELY NEW in the current quarter i.e. 2020-Q2.

[az38]

@ritu24raj 

I see your original post and I do not understand why the row Lenovo 2017-Q1 is not marked as new in your example. It was first mention of Lenovo?

you need newbies only for 2020-Q2 or for each quarter?

[ritu24raj]

@az38 Thanks for the prompt reply, Lenovo was already present in 2017-Q1 and it again in 2020-Q2, thats the reason it was not there.

Also you correctly mentioned, I need only the newbies for the current quarter i.e. 2020-Q2.

[az38 · Accepted solution]

@ritu24raj 

try smth like

Result = 
var _curQ = CONCATENATE(FORMAT(TODAY(), "YYYY-"), CONCATENATE("Q", FORMAT(TODAY(), "Q")))
var _firstOccur = CALCULATE(MIN('Table'[Closed Year-Quarter]), ALLEXCEPT('Table', 'Table'[Account Names]))
var _thisYQ = MAX('Table'[Closed Year-Quarter])
return
IF(_curQ = _thisYQ && _firstOccur = _thisYQ, MAX('Table'[Account Names]), BLANK())

[ritu24raj]

Thank you @az38 for the help!

[ritu24raj]

@az38  The code shared is not working. Also, to give more clarity sharing the exact data set.

Test_Result =

VAR _curQ =

    CONCATENATE (

        FORMAT ( TODAY (), "YYYY-" ),

        CONCATENATE ( "Q", FORMAT ( TODAY (), "Q" ) )

    )

VAR _firstOccur =

    CALCULATE (

        MIN (

            CONCATENATE (

                LASTNONBLANK ( 'Table'[Close Year], 1 ),

                LASTNONBLANK ( 'Table'[Close Quarter], 1 )

            )

        ),

        ALLEXCEPT ( Customer, ' Customer [Customer Name] )

    )

VAR _thisYQ =

    MAX (

        CONCATENATE (

            LASTNONBLANK ( 'Table'[Close Year], 1 ),

            LASTNONBLANK ( 'Table'[Close Quarter], 1 )

        )

    )

RETURN

    IF (

        _curQ = _thisYQ

            && _firstOccur = _thisYQ,

        MAX ( 'Account'[Account Name] ),

        BLANK ()

    )

Account Name	Close Date Ops Year	Close Date Ops Quarter
A	2015	4
B	2017	1
C	2018	4
D	2016	2
E	2019	3
F	2019	1
F	2018	4
G	2018	4
H	2020	2
I	2019	1
I	2017	4
I	2019	4
J	2020	2
K	2017	2
K	2018	4
L	2017	2
L	2018	4
M	2017	1
N	2019	1
O	2019	3

[az38]

@ritu24raj 

share full and correct data model

I see as minimum 2 tables in your statement

[ritu24raj]

Sharing details via Linkedin