Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
ewu
Helper I
Helper I

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

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? 

4 ACCEPTED SOLUTIONS
ryan_mayu
Super User
Super User

@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")

 

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

pls try to create a column , not a measure





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

ewu_0-1677632564320.png

Here is a better image shot

View solution in original post

@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))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

20 REPLIES 20
ewu
Helper I
Helper I

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!

 

you are welcome





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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. 

@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")




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




ryan_mayu
Super User
Super User

@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")

 

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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)?  

Screenshot 2023-02-21 134649.png

 

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.

pls try to create a column , not a measure





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!!!

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Raw data:   Results:  
Customer MonthValue Churned MonthValue
AOct-22100 ADec-22110
ANov-22110 DFeb-23680
BOct-221000    
BNov-221000 Results:  
BDec-221200 NewMonthValue
BJan-231200 CJan-23500
BFeb-23900 DNov-22700
CJan-23500    
CFeb-23530    
DNov-22700    
DDec-22720    
DJan-23680    

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

 

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

@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))

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Thanks I will take a look tomorrow. 

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

ewu_0-1677632564320.png

Here is a better image shot

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?

 

ewu_0-1677996955724.png

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?

 

 

 

 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.