Evaluating success based on two var dates with two different tables

[jacmanda]

Good day! 

I have a problem I was hoping the community could assist me with.

We are an Internet company that has a subscription-based model where our customer has a 12-month subscription to our site. We have a customer retention team that focuses on a customer that show Churn signs, like not logging into the website the last 30 days.

So our customer representatives work in google sheets - and we import customers into their workbook once a week. (G-Consultant) This provides us with a customer key (b_id) and date in. In another table I have all the log inns from our customer. (log) [b_id], [log_in_date]

So representitive A get's Customer B the 24.01.2019. - He then has 45 days to get that customer to log-in (Which will show up in my log table.) I want to create a measure where i can get the output "Success" or "Fail" if the customer shows up in the (log) table between 24.01.2019  and today()+45.

Thanks in advance to anyone who can help 🙂

[jacmanda]

@v-frfei-msft @parry2k

Anyone...? Please

[v-frfei-msft]

Hi @jacmanda,

To create a measure as below.

Measure = IF(CALCULATE(COUNTROWS('log'),FILTER('log','log'[trig_dato]>=DATE(2019,01,24) && 'log'[trig_dato]<=TODAY()+45))>1,"Success","Fail")

For more details, please check the pbix as attached.

Regards,

Frank

[jacmanda]

Thank you so much for your reply - however I think we might need some modification. Mostly because the filter has to be based on the inndate [kp_dato] in g-consultant and inndate {kp_dato] + 45 days as the end point. 

What i thought was to create a calculated coloum in g-consultant. So we calculate the measure based on what they have in their workbook.

So thought something like this:

var inndate=[kp_date] 

var enddate= if today() > [kp_date]+45;[kp_date]+45;today()

return

if (log[trig_dato]>=inndate && log[trig_dato] <=enddate;"Success";"Fail")

However I don't know how i can scan the log table based on the b_id in the G-Consultant table?? 

@v-frfei-msft

[v-frfei-msft]

Hi @jacmanda,

I think we can use USERELATIONSHIP to work on it. Could you please share your pbix to me? Please upload the files to One Drive and share the link here.

Regards,

Frank

[jacmanda]

pbix

@v-frfei-msft

[parry2k]

@jacmanda Can you repost the images?

[jacmanda]

i posted them in a reply @parry2k

[jacmanda]

G-consultant tablelog tableconnections