cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ritu24raj
Helper II
Helper II

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

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 NamesClosed Year-QuarterResult Accounts
Intuit2014-Q1 
SBI2014-Q1 
Chevron2016-Q1 
Google2016-Q4 
Lenovo2017-Q1 
Oracle2017-Q2 
FB2017-Q4 
HP2019-Q3 
Infosys2019-Q4 
Cvent2020-Q2Cvent
FB2020-Q2 
Dell2020-Q2Dell
Google2020-Q2 
Lenovo2020-Q2 

 

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

1 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())

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

13 REPLIES 13
Icey
Community Support
Community Support

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] )
    )

account.jpg

 

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
Super User III
Super User III

@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.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





@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.

@amitchandak

@Greg_Deckler

@az38

@Ashish_Mathur 

@Mariusz  - Can you please help me in this

@ritu24raj 

why does [Result Accounts] is empty for row 

Lenovo 2017-Q1

?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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.

@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?


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

@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.

@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())

do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

View solution in original post

Thank you @az38 for the help!

@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 NameClose Date Ops YearClose Date Ops Quarter
A20154
B20171
C20184
D20162
E20193
F20191
F20184
G20184
H20202
I20191
I20174
I20194
J20202
K20172
K20184
L20172
L20184
M20171
N20191
O20193

 

@ritu24raj 

share full and correct data model

I see as minimum 2 tables in your statement


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Sharing details via Linkedin

Helpful resources

Announcements
User Groups Public Preview

Join us for our User Group Public Preview!

Power BI User Groups are coming! Make sure you’re among the first to know when user groups go live for public preview.

March Update

Check it Out!

Click here to read more about the March 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.