D13 Calculate Lost 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

[CurbalEN]

For question 14, count customers on the customer table, not the order table!

[jaimemolina]

this series is some of the best stuff out there because you can try it out yourself and then get the solution. I have learned a lot. Gracias Ruth!!!

[kahhengyeong]

I got 4 and I appreciate that it's also considered correct because if it were my boss, he would also insist it is 4 as we need to see all customers, and not only customers in the orders table :)

[shadfrigui]

Same pattern as some of the previous measures. Group, filter, and aggregate.
1. Create a table by *grouping* the "Orders" table by a specific column and adding an additional column using ADDCOLUMNS + SUMMARIZE.
2. *Filter* the created table to meet some criteria using FILTER.
3. *Aggregate* the filtered table using COUNTROWS, AVERAGEX, etc.

I like to stick to this approach because it has some similarities with SQL. Although in SQL, the order of execution is different.

YouTube is deleting my comments. I think that DAX code is the problem.

[dirksmeyers]

Hello Ruth. It took time to reach level nr 13. Not that it was so hard, but I had to review some of your previous lovely DAX Fridays to understand completely the logic of DAX. Anyway RESPECT for all the Curbal-nautes who could find every day and so fast the correct answers!!! I base myself on the fables of Lafontaine with the hare and the tortoise. I take the role of the tortoise and I'm so happy that there is also Christmas this year. I will be on time :-D. Thanks for your video's!

[bhuvaneshwariraman]

Thank you for video. One question- why did you save the date in a variable instead of adding the date directly in the filter?

[jamiedavies]

This was my approach, which gave the correct answer in this instance.

Day 13 - Lost Customers = COUNTROWS(
FILTER(
SUMMARIZE(Orders, Orders[CustomerID], "LastTransaction", Year(MAX(Orders[OrderDate]))), [LastTransaction] <> 2021))

[Maartenmarsje]

Thanks! Still loving the challenge. I think you should not use the =< operator in this solution, because the equal sign refers to the current year. And that is not matching with the question right?

[jimmorrison]

My rationale was to return a list of customers that placed an order prior to 2021, but didn't place an order in 2021. Generate two filtered tables of distinct customerIDs that satisfy these two criteria, then apply EXCEPT.

[SeneXeL]

My solution:

Lost Customers =

COUNTROWS(
FILTER
(
SUMMARIZE(
Orders,
Orders[CustomerID],
"Last Date",
LASTDATE(Orders[OrderDate])
), [Last Date] < DATE(2022, 01, 01)

)

[baskis]

Hola Ruth,
Mi Soluciión =
VAR _CurrentYear= date(YEAR(TODAY()), 1, 1)
VAR _ClientsAnteriors= CALCULATETABLE(DISTINCT(Orders[CustomerID]), Orders[OrderDate]<_CurrentYear)
VAR _ClientsActuals= CALCULATETABLE(DISTINCT(Orders[CustomerID]), Orders[OrderDate]>=_CurrentYear)
VAR _ClientsPerduts= EXCEPT(_ClientsAnteriors, _ClientsActuals)
RETURN
COUNTROWS(_ClientsPerduts)

Me encanta tu reto¡¡¡

[ruchitashah]

Day 13 =
var no = SUMMARIZE(Customers, Customers[CompanyName], "Last Order", MAX(Orders[OrderDate]))
var los_cust = FILTER(no, YEAR([Last Order]) <> YEAR(TODAY()) && YEAR([Last Order]) <> BLANK())
Return COUNTROWS(los_cust)

[mehdihammadi]

Got this one and here the formula I used
# Lost customers =
// Day 13 : how many lost customers (no purchase in current year (2021) ?
// This measure do not consider customers that have never purchased
// Retrieve the current year
VAR _CurrentYear = YEAR(TODAY())
// Get the year of last order date for each customer
VAR _CustomersOrdersDates = SUMMARIZE(
'Orders',
'Orders'[CustomerID],
// Filter to the ones who have only ordered before the current year
// Count the customers
VAR _NbrCustomers = COUNTROWS( )
Return
_NbrCustomers

[antoniogarrido]

EX13 =
Var LastOrder = SUMMARIZE(Orders, Orders[CustomerID], "LastOrderDate", LASTDATE(Orders[OrderDate]))
Var CountCustomers = FILTER(LastOrder, [LastOrderDate]>=DATE(2021, 1, 1)&&[LastOrderDate]<=DATE(2021, 12, 1))
RETURN

COUNTROWS(CountCustomers)

[joelngige]

My solution for Day 13, seems like the one i posted earlier was not saved or deleted.
Day 13: How Many Lost Customers (no Purchases in current year) in 2021? =

VAR NosalesCustomers2021 =
FILTER(
ADDCOLUMNS(
CALCULATETABLE(
VALUES(Customers[CompanyName]), 'Calendar'[Year] IN {2020, 2021} ),
"Sales2021", CALCULATE([Total Revenue], 'Calendar'[Year] = 2021),
"Sales2020", CALCULATE([Total Revenue], 'Calendar'[Year] = 2020)),
[Sales2021] <= 0 && [Sales2020]> 0)

RETURN