Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi guys,
I am trying to build a report that can tell me whether a customer is new / current / lost.
This depends on the when they last ordered something at the company.
I thought I had it figured out with a summarize table, but the column will sometimes give all three different results for one single customer. It needs to be a column because we need to be able to sort on the customer status.
Below you can find what I use now in my summarize table:
Hi,
What is the purpose of the Status column there? Isn't this the result you are expecting? Also, would you be OK if Status is a measure (not a calcuated column formula)?
Hi @Ashish_Mathur ,
I would be totally fine with a measure. However, I can't use a measure to filter the results.
That's why I am using a calculated column.
The result I would want is that there is one status for each accountnumber. Not (a different) one for each individual record
Hi,
You may download my solution PBI file from here. I have used only measures to solve the problem which is ahy i can select Current, New and Lost in a slicer. No value will appear for Lost because we do not have data prior to 2017. Once you add sales data prior to 2017 to your dataset, you will see the customers lost figure aas well.
Hope this helps.
Hi @Anonymous ,
You could unpivot these 4 columns as one column in the query editor. Then you could remove "Sales_" and only keep years.
Then you could use SUMMARIZE().
Table =
SUMMARIZE (
Sheet1,
Sheet1[Accountnumber],
"Status", IF (
CALCULATE (
SUM ( Sheet1[Value] ),
FILTER (
Sheet1,
Sheet1[Attribute]
> ( YEAR ( TODAY () ) - 3 )
&& Sheet1[Attribute] < YEAR ( TODAY () )
)
) > 0,
"Current",
IF (
CALCULATE (
SUM ( Sheet1[Value] ),
FILTER ( Sheet1, Sheet1[Attribute] = YEAR ( TODAY () ) )
) > 0,
"New",
"Lost"
)
)
)
@Anonymous , Try based on date like
Summarize(Customer,Customer[Csutomer],"Status",
Switch( True(),
datediff(max(Reservations[Reservations Date]),today(),Year) >= 3 ,"Lost",
datediff(max(Reservations[Reservations Date]),today(),Year) >= 0 ,"Current",
"New"))
Hi @amitchandak thanks for the quick reply,
sadly this still gives all three results for most customers.
Hi @Anonymous ,
First thing I don't understand your DAX measure, looks like it has got error.
Can you tell me the check points correctly for different customer status, so that I can suggest a DAX for it?
Try sharing some sample data with example.
Thanks,
Pragati
Hi @Pragati11 ,
it is actually part of a summarize table, that's why it might look a little funny.
But I made some sample data in excel which I hope might help. The idea is that any costumer that didn't order after 2017 is considered a lost costumer. If they ordered between 2017 and 2020 they are current and if they didn't order in 2018 or 2019, but they did in 2020 they are considered new.
Let me know if you need anything else.
Kind regards,
Queenie
@Anonymous , Assume you have 4 different column , then you need to have something like this
Switch(true(),
not(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "New" ,
not(isblank([Sales_2020])) && (not(isblank([Sales_2019])) || not(isblank([Sales_2018]))), "Current" ,
(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "Lost"
)
Hi @amitchandak ,
It is 4 different columns, but it is grouped by reservation date. This means that I have multiple records for one year and that is probably why my first idea doesn't work.
Kind regards
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
93 | |
86 | |
80 | |
69 | |
68 |
User | Count |
---|---|
226 | |
129 | |
119 | |
83 | |
77 |