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.
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
Solved! Go to 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
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
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
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
@v-jiascu-msftyou're welcome, and thanks for using www.daxformatter.com, the code is much easier to read (for me)! 🙂
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"
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
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |