Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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?
Solved! Go to Solution.
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&¬(ISBLANK(_last))&&'Table'[value]<>0,"Y")
Proud to be a Super User!
pls try to create a column , not a measure
Proud to be a Super User!
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))
Proud to be a Super User!
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
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.
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&¬(ISBLANK(_last))&&'Table'[value]<>0,"Y")
Proud to be a Super User!
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&¬(ISBLANK(_last))&&'Table'[value]<>0,"Y")
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)?
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
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?
Proud to be a Super User!
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 |
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?
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?
Proud to be a Super User!
Hi Ryan, this is because B and C have values in Feb 2023, the lastest month.
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))
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.
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?
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?
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
127 | |
109 | |
93 | |
70 | |
67 |