cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

4 ACCEPTED SOLUTIONS
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&&not(ISBLANK(_last))&&'Table'[value]<>0,"Y")``````

Proud to be a Super User!

Super User

pls try to create a column , not a measure

Proud to be a Super User!

Helper I

Here is a better image shot

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!

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

Super User

you are welcome

Proud to be a Super User!

Helper I

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.

Super User

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

Proud to be a Super User!

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&&not(ISBLANK(_last))&&'Table'[value]<>0,"Y")``````

Proud to be a Super User!

Helper I

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

Helper I

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.

Super User

pls try to create a column , not a measure

Proud to be a Super User!

Helper I

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

Super User

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

Proud to be a Super User!

Helper I
 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
Super User

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!

Helper I

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

Super User

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

Proud to be a Super User!

Helper I

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

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!

Helper I

Thanks I will take a look tomorrow.

Helper I

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

Helper I

Here is a better image shot

Helper I

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?