Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 | |
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 |
2020-Q2 | ||
Lenovo | 2020-Q2 |
Is there a way out to write a DAX for this, please help
Solved! Go to Solution.
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())
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.
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
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.
why does [Result Accounts] is empty for row
Lenovo | 2017-Q1 |
?
@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.
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?
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())
@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 |
share full and correct data model
I see as minimum 2 tables in your statement
Sharing details via Linkedin
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |