D12 Calculate new customers | 25 Days of DAX Fridays! Challenge

Welcome to 25 days of DAX Fridays! Challenge, where one DAX puzzle will be available every day for you to solve from the first of December to the 25th of December.

How to participate in the challenges:

IMPORTANT!

1. Make sure you download my version of Northwind (on the link mentioned above) if you want to get the same results as me, as I have modified the dataset.
2. Make sure you refresh the dataset daily and compare the results with mine the same day.
3. The level of difficulty will vary, although I have tried to make the questions in such way that you will be able to solve them with easy or elegant DAX.
4. Once you have the answer you can let me know in the comments or tweet it with the hashtag #25DaysOfDAXFridays #curbal.
5. It is never too late to start the challenge ;)
6. Have fun.

SUBSCRIBE to learn more about Power and Excel BI!

Our PLAYLISTS:

☼☼☼☼☼☼☼☼☼☼

POWER BI COURSES:

Want to learn Power BI? How about you take one of our courses? Here you can find the available courses:

☼☼☼☼☼☼☼☼☼☼

ABOUT CURBAL:

************

MY GEAR: You often ask me what software I use to edit my videos, so I thought I let you know in the description and give you a discount code so you can get it too!
COUPON: CURBAL10
Note, if you purchase the software using the link or code, I will receive a small commission and you will indirectly support my channel. Thanks in advance!

************

QUESTIONS? COMMENTS? SUGGESTIONS? You’ll find me here:
Twitter ► @curbalen, @ruthpozuelo

#CURBAL #SUBSCRIBE

[joelngige]

I started learning DAX Jan 2021 but for the first time in my DAX journey I have found myself really thinking critically about the questions and generating my own DAX code and testing the same before publishing the answers. Am amazed by how far the past 13 days have helped me come out of that comfort zone. It's not about getting all the answers correct but the fact that one can now swim alone in the "DAX verse" and come out of the side. This improves one's self-confidence immensely.

@Ruth, i have watched most of your videos on YouTube which are always great but i cannot thank you enough for making this challenge as this is a game changer in learning DAX. Hope you will be inspired to make another one. THANK YOU!!! THANK YOU!!!!

[drizzle]

At this point, I must confess, the SUMMARIZE function just makes everything much easier and simpler for me. Thank you Ruth!

[MattiasGregersen]

A couple of lessons for me: A) The power of "manually figuring out the result" -> Replicate with DAX, B) Summarize-function. I have never used summarize before and it is amazing and seeing I can use variables in there as well <3

[edwinsillop]

Thank you a lot for the challenges. I'm learning a lot =D
In my case, I have an old version of the Northwind Database but I got the solution too (your solution is amazing).
As you showed in the previous day, I used the following DAX:

Day_12=
SUMX(Customers,

IF(YEAR(CALCULATE(FIRSTDATE(Orders[OrderDate])))=1998, 1, 0))

[simoneferrari]

Day 12 - # New Customers in 2021 =
COUNTROWS(
FILTER(
Customers,
>= DATE ( 2021, 01, 01))
))

[AndrasGulyas-exho]

Thank you for the exercises, they are really useful!

[vida]

Your solution and step by step explanation was so beautiful. My sulution was:
New Customers in Current Year =
Var CurrentYear = YEAR(today())
Var Result =
COUNTROWS (
FILTER (
ADDCOLUMNS (
VALUES ( Orders[CustomerID] ),
"Previous Orders",
CALCULATE (
COUNTROWS ( Orders ),
FILTER ( ALL ( 'Calendar' ), Calendar[Year] < CurrentYear )
)
),
[Previous Orders] = BLANK ()
)
)
Return Result

[ramakumarguntamadugu]

thank you very much for sharing this great content.. looking forward for many quizzes / tasks like this ..really appreciate it.

[joelngige]

My solution utilized the EXCEPT Function as follows;

Day 12.2: How many New customers (first purchase in current year) in 2021? =

VAR CustomersPrior2021 =
CALCULATETABLE(
DISTINCT(
Orders[CustomerID]), YEAR(Orders[OrderDate]) <= 2020
)

VAR Customers2021 =
CALCULATETABLE(
DISTINCT(
Orders[CustomerID]), YEAR(Orders[OrderDate]) >= 2021
)

RETURN

COUNTROWS(EXCEPT(Customers2021, CustomersPrior2021))

[naveen]

I think title can be no. of customers after a specified time. otherwise new customer can mean if there is no purchase earlier and had only one purchase

[AgulloBernat]

here's my approach
Day 12 - New Customers Current Year =
VAR currentYear = [Current year]
VAR customersUntilPreviousYear =
CALCULATETABLE(
VALUES( 'Orders'[CustomerID] ),
'Calendar'[Year] < currentYear
)
VAR customersCurrentYear =
CALCULATETABLE(
VALUES( 'Orders'[CustomerID] ),
'Calendar'[Year] = currentYear
)

VAR newCustomers =
EXCEPT( customersCurrentYear, customersUntilPreviousYear )

VAR numOfNewCustomers =
COUNTROWS(newCustomers)

RETURN
numOfNewCustomers

[branislavpoljasevic]

A slightly different approach :)

NewCustomersin2021 =


VAR _2020 =
CALCULATETABLE(
VALUES(Orders[CustomerID]),
'Calendar'[Year] = 2020
)

VAR _2021 =
CALCULATETABLE(
VALUES(Orders[CustomerID]),
'Calendar'[Year] = 2021
)

VAR _result =
EXCEPT(
_2021,
_2020
)

RETURN

COUNTROWS(_result)

[ferasalramli]

Hi Ruth,

I have a question, if I need to use Calendar[Fiscal Year] column instead of define a date in dax.

I get an error when use it with any virtual table syntax.

Thanks

[MariaMs]

Hello,

Here is my solution:

Day 12 =

var firstdatetable =
SUMMARIZE(Orders,
Orders[CustomerID],
"firstdate",
FIRSTDATE(Orders[OrderDate])
)
return

CALCULATE(
DISTINCTCOUNT(Orders[CustomerID]),
filter(
firstdatetable,
year([firstdate])=[Current year]
)
)

[pcosta]

I have 8 for result!

Day 12 =
COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[CustomerID],
"CountNew",
YEAR(MIN(Orders[OrderDate]))
),
[CountNew]=2021
)
)

[ShijuKattarkandy]

So exciting to watch your challenges. Just wondering if I can find the Number of New Customers during a period? Eg. I want to find how many new customers with a date slicer which has (Year and Month).

[ahmadmohamadmohamadalmanza]

Day 12 =
COUNTROWS(
FILTER(
SUMMARIZE(
Orders,
Orders[CustomerID],
"firstorderdate",
YEAR(FIRSTDATE(Orders[OrderDate]))),
[firstorderdate] = 2022
))

[akshayachandrasekar]

Hi Ruth, I noticed that in most of the dax code, VALUES function is used to get the unique values from a column, why can't we use DISTINCT? Can you please clarify this doubt

[ivanrybalchenko]

Hello, Ruth!
I added column to customer table:
FirstOrder =
Calculate(
FIRSTDATE(Orders[OrderDate]),

)

After created a measure:
NewCustomers =
Var Dates =

Return
Calculate(
COUNTROWS(VALUES(Orders[CustomerID])),
Orders[OrderDate] in (Dates) && YEAR(Orders[OrderDate])=2021
)

But it gaves me the result = 13 against correct 8, what am I doing wrong, can you ore somebody explain, PLEASE?

[baskis]

Mi solución : COUNTROWS(
FILTER(
SUMMARIZE(Orders, Customers[CompanyName], "fistdate", FIRSTDATE(Orders[OrderDate])),
[fistdate]>date(2020, 12, 31)))