Get difference from 2 month by filtering 1 month to be zero

[ewu]

Hi I have a table that looks like this:

customer, month, value

A, Oct, 0

A, Nov, 500

A, Dec, 600

B, Oct, 1000

B, Nov, 800

B, Dec, 0

C, Oct, 300

C, Nov, 400

C, Dec, 200

.... thousands of customers, each with 3 month data

I need to get a list of customers who left in a month (eg. B left in Dec), and who came on board in a month (eg A joined in Nov). I don't care if a customer's value changed from month to month as long as the value is not zero.

Could someone please help? 

[ryan_mayu · Accepted solution]

@ewu 

you can try this

Left = 
VAR _last=maxx(FILTER('Table','Table'[customer]=EARLIER('Table'[customer])&&'Table'[month]=EDATE(EARLIER('Table'[month]),-1)),'Table'[value])
return if(not(ISBLANK(_last))&&'Table'[value]=0,"Y")

Join = 
VAR _last=maxx(FILTER('Table','Table'[customer]=EARLIER('Table'[customer])&&'Table'[month]=EDATE(EARLIER('Table'[month]),-1)),'Table'[value])
return if(_last=0&&not(ISBLANK(_last))&&'Table'[value]<>0,"Y")

[ryan_mayu · Accepted solution]

pls try to create a column , not a measure

[ewu · Accepted solution]

Here is a better image shot

[ewu]

Ryan, I was looking for help about how to do 1+1+1+1..=10. I was hoping to get answers like 1+1+1...=2+1+1+1...=3+1+1..=10.

Now you give me an anwer that this equals 1*10, and that 10/1=10.

But thank you so much for the beautiful answer, and for spending the time! This tells me how much I still need to learn, starting with EARILER, EDATE, etc.

Let me digest and I may come back for specific questions about your DAX.

Thanks again!

[ryan_mayu]

you are welcome

[ewu]

Ryan,

EARLIER seems to be a painful concept, at least for me and many others. Google says it can be replaced with VAR or variable. Would it be possible that you do this without using EARLIER but VAR instead?

Thanks a lot. 

[ryan_mayu]

@ewu 

pls try this

Left = 
VAR _customer='Table'[customer]
VAR _month='Table'[month]
VAR _last=maxx(FILTER('Table','Table'[customer]=_customer&&'Table'[month]=EDATE(_month,-1)),'Table'[value])
return if(not(ISBLANK(_last))&&'Table'[value]=0,"Y")

Join = 
VAR _customer='Table'[customer]
VAR _month='Table'[month]
VAR _last=maxx(FILTER('Table','Table'[customer]=_customer&&'Table'[month]=EDATE(_month,-1)),'Table'[value])
return if(_last=0&&not(ISBLANK(_last))&&'Table'[value]<>0,"Y")

[ryan_mayu · Accepted solution]

@ewu 

you can try this

Left = 
VAR _last=maxx(FILTER('Table','Table'[customer]=EARLIER('Table'[customer])&&'Table'[month]=EDATE(EARLIER('Table'[month]),-1)),'Table'[value])
return if(not(ISBLANK(_last))&&'Table'[value]=0,"Y")

Join = 
VAR _last=maxx(FILTER('Table','Table'[customer]=EARLIER('Table'[customer])&&'Table'[month]=EDATE(EARLIER('Table'[month]),-1)),'Table'[value])
return if(_last=0&&not(ISBLANK(_last))&&'Table'[value]<>0,"Y")

[ewu]

Hi Ryan,

Thanks a lot, and I understand it now. But perhaps programming is all about "counter-human" thinking. How did you come up with that thought process? Why use month-1,  not month+1 (of course if so, other conent needs to be adjusted)?  

[ewu]

Ryan,

I tried to do what you wrote. But it just doesn't allow me to write anything behind "VAR Customer=". Nothing shows up automatically as expected for me to choose. Could you please let me know why?

Thanks.

[ryan_mayu · Accepted solution]

pls try to create a column , not a measure

[ewu]

Hi Ryan,

Just saw your messge somehow today... Thanks a lot. It worked.. almost...

The new issue is that once I load the raw data, I find actually the 0-value rows do not exist. So an Nov churned customer = a customer with value in October only, and no other rows for this customer show up

How would this DAX be? Thanks!!!

[ryan_mayu]

could you pls update the sample data and provide the expected output as well?

[ewu]

Raw data:				Results:
Customer	Month	Value		Churned	Month	Value
A	Oct-22	100		A	Dec-22	110
A	Nov-22	110		D	Feb-23	680
B	Oct-22	1000
B	Nov-22	1000		Results:
B	Dec-22	1200		New	Month	Value
B	Jan-23	1200		C	Jan-23	500
B	Feb-23	900		D	Nov-22	700
C	Jan-23	500
C	Feb-23	530
D	Nov-22	700
D	Dec-22	720
D	Jan-23	680

[ryan_mayu]

why we don't have B in the results?

why new only have C and D, what about the first row for A? is that also new?

[ewu]

Good question. Lets assume Oct is the first month of operation, so any new/churn starts in Nov

[ryan_mayu]

why churned is only A and D? why the last rows for B and C are not churned? 

[ewu]

Hi Ryan, this is because B and C have values in Feb 2023, the lastest month. 

[ryan_mayu · Accepted solution]

@ewu 

pls try this

New = 
VAR _min=min('Table'[Month])
VAR _min2=CALCULATE(min('Table'[Month]),ALLEXCEPT('Table','Table'[Customer ]))
return if(_min2>_min&& _min2='Table'[Month],"y")

churned = 
VAR _min=min('Table'[Month])
VAR _max=CALCULATE(max('Table'[Month]),ALLEXCEPT('Table','Table'[Customer ]))
return if(_max<EOMONTH(today(),-1)+1&&_max='Table'[Month],"y")

churnedmonth = if('Table'[churned]="y",EDATE('Table'[Month],1))

[ewu]

Thanks I will take a look tomorrow. 

[ewu]

Here it is Ryan. I should have made it more clear.  Thanks a lot. 

[ewu · Accepted solution]

Here is a better image shot

[ewu]

Ryan, 

It worked, and I revised a bit with some weird data (some joined and left quickly, etc.) Thanks so much. I have a new challenge now. Could you please help me?

How can I get from the top 2 tables to the bottom table? If I can learn to understand what you will write and even be able to do it myself later, Is this considered achieving advanced DAX already?