topic Re: How to get YoY change based on Customer types of this year? in DAX Commands and Tips

How to get YoY change based on Customer types of this year?

BBZ221109 — Wed, 09 Nov 2022 14:52:27 GMT

Hi everyone, I'm very new to DAX-world. Please help me, and I'm struggling with a filter problem; Currently, I'm doing a customer retention analysis. Every customer may have different tagging of customer types( New customers, Growing customers, Declining customers, or Churned customers). I determined the tagging of the last month as customer types for each customer; let's called it Last_month_tagging. See the matrix below:

Last_month_tagging	Sales of this year	Sales of last year	YoY change
New Customers	100	0	100
Growing Customers	500	250	250
Declining Customers	200	300	-100
Churned Customers	0	100	-100
Total			150

I want to use the last_month_tagging to determine the customer names and their sales for this year, and I want to get the sales of last year from the same customers from this year, which means the measure sales last year should not be affected by column last_month_tagging. YoY change is the sales difference of the same customers, and it should also not affect by column last_month_tagging.

However, using last_month_tagging, Power BI will deliver the sales of last year based on last_month_tagging instead of customer names. I appreciate any help you can provide!

Re: How to get YoY change based on Customer types of this year?

amitchandak — Thu, 10 Nov 2022 02:32:57 GMT

BBZ221109 , if tagging is a column then you can create YOY measure using calculations like

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),previousyear('Date'[Date]))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

//Only year vs Year, not a level below

This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])

ore refer

Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

In this you have use the Year and Last year measure to sum , one type is finalized

example

Sumx(filter( values(Customer[Customer]) , not(isblank([This Month])) && not(isblank([Last Month])) ) , [This Year])

Sumx(filter( values(Customer[Customer]) , not(isblank([This Month])) && not(isblank([Last Month])) ) , [Last Year])

Re: How to get YoY change based on Customer types of this year?

v-yueyunzh-msft — Thu, 10 Nov 2022 03:47:29 GMT

Hi ， BBZ221109

Here are the steps you can refer to :
(1)This is my test data :

(2)We can create three measure :

Sales of last year = var _t = FILTER( 'Customer' , YEAR('Customer'[Date]) = YEAR( TODAY())-1) return SUMX(_t , [Sales])+0

Sales of this year = var _t = FILTER( 'Customer' , YEAR('Customer'[Date]) = YEAR( TODAY())) return SUMX(_t , [Sales])+0

YoY change = [Sales of this year] - [Sales of last year]

(3)Then we can put these measure on the visual and then we can meet your need , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Re: How to get YoY change based on Customer types of this year?

BBZ221109 — Tue, 15 Nov 2022 20:55:05 GMT

Hi Aniya, Thank you for reply! I had a busy weekend, so I couldn't answer your question immediately, apologize!
I have total sales from Januar 2020 to November 2022. I want to calculate the YoY change of Single customer fron Januar 2020 to November 2022. I'm doing Net retention analysis, so each customer has their own Tagging ( Customer Status: New Customers, Growing Customers, Churned customers and Declining customers).
Measure this year = Total Sales
Measure last year = calculate (Total sales, sameperiodlastyear(output[period].[date]))
YoY change = measure this year - measure last year.

At the end I want have a matrix: Tagging (customer status) as first category column, period.[date] as horizontal column and YoY change as value in the matrix. The customer status will be determined every month. So there are many Taggings for a certain customer. If I use last month Tagging as the Tagging for each customer in the whole period, the result is not correct, especially for new customers.
There are two problems:
1) if use customer status
should I use last month Tagging?
2)My idea was that using last month Tagging to determine customer status for each customer. Then calculate YoY change of these customer regardless their customer status. However, If I use customer status for measure this year, then it will affect also measure last year and YoY change.

I'm a bit confused about if my idea is correct or not. Please help me with this problem, I'll appriciate for any help you can provide! 🙏

Re: How to get YoY change based on Customer types of this year?

BBZ221109 — Tue, 15 Nov 2022 20:55:24 GMT

Hi Admit!, Thank you for reply! I had a busy weekend, so I couldn't answer your question immediately, apologize!
I have total sales from Januar 2020 to November 2022. I want to calculate the YoY change of Single customer fron Januar 2020 to November 2022. I'm doing Net retention analysis, so each customer has their own Tagging ( Customer Status: New Customers, Growing Customers, Churned customers and Declining customers).
Measure this year = Total Sales
Measure last year = calculate (Total sales, sameperiodlastyear(output[period].[date]))
YoY change = measure this year - measure last year.

I'm a bit confused about if my idea is correct or not. Please help me with this problem, I'll appriciate for any help you can provide! 🙏

Re: How to get YoY change based on Customer types of this year?

v-yueyunzh-msft — Wed, 16 Nov 2022 03:55:40 GMT

Hi , BBZ221109

According to your description, a customer has many tagging, and you want to define the customer tagging on the last month. Right?

If this , i think you can create a calculated column to define what is this customer tagging is in the whole period.

Then we can put this custom column on the visual and we will filter the customers we need .

If this method does not you need , can you share the sample data as a table and put what you want in the end as a table so that we can help you better.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly