custom column from counting the rows after filtering a table for a date 6 months ago

[Anonymous]

Hi Community,

I need some help with creating a custom column in power query of dataflow.

I am trying to find the number of providers churned in a particular account within a period of six months before, from the churn date of that provider.

The logic should work as follows:

1. Go to provider ID cell.
2. Find the churn date for a provider.
3. Calculate the date 6 month previous to the churn date.
4. Filter the table to only have the data in the table that have churn date greater than or equal to the date 6 months ago from the churn date for that provider.
5. Then, filter the table for the account related to that provider.
6. Finally, count the rows, and put it in the next cell to right in another column.

eg:  Lets consider the ID 1042-1034.  It churned on 9th April 2020, the date 6 months previous to it is 9th Oct 2019.  The ID 1042-1034 is related to Account_1.  Only 4 providers have churned between  9th Oct 2019 and 9th April 2020, so the cell right to it should have 4. (expected output)

I have only been able to find the date six months ago from today’s date so far using this blog.

Sample data can be found here.

Any partial suggestion is also appreciated.

Thanks and Regards

[v-janeyg-msft]

Hi ,  @Anonymous 

According to your description, I think you can create a custom column in PQ.

Like this:

= Table.AddColumn(#"Renamed Columns", "Custom", each let
st = Date.AddMonths([Churn_Date],-6),
tab = Table.SelectRows(
    #"Renamed Columns",
    (x)=>x[Account_Name]=[Account_Name] and x[Churn_Date]>=st and x[Churn_Date]<=[Churn_Date]
),
count = List.Count( List.Distinct(tab[Provider_ID]))
in
count)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

This is is not getting the expected output.  In the file, we have one ID for 1042-1034 and it should give 4 in the next cell as 4 providers have churned in Account_1 within last six months of the churn date for this provider.  

This formula is giving me somehow multiple IDs with varied numbers across cells.  Please check the screenshot of the output once and desired output.

[v-janeyg-msft]

Hi, @Anonymous 

No obvious logic can be seen from your two screenshots and I am confused by your description. Can you provide sample data with correct data structure? So we can help you soon.

Best Regards

Janey Guo

[Anonymous]

Hi @v-janeyg-msft 

I am sorry that I have not been able to explain the logic clearly.  Let me explain it again.  Please find the logic PDF here.

Hope, this is helpful for you to understand the logic.  Lemme know if I can explain any part further.

Please find the sample data here.  The data has been shared via google drive links.  Lemme know if you have any difficulty accessing the logic PDF or the sample data.

Thank you.

[v-janeyg-msft]

Hi, @Anonymous 

I'm sorry. I tried hard to understand your logic. It’s really hard to understand by comparing your description and data screenshots. My colleague is just as confused as I am, so it is difficult to help you further. Can you tell me the difference between the result of my first reply and the result you want? Can you show the image(You can directly enter the correct column for reference) of the result you want in pq without just say one or two?

Best Regards

Janey Guo

[Anonymous]

Okay @v-janeyg-msft  no problem.  I will try again to explain the logic with some screenshots of expected outcomes at each step.  Please check this once.  Let me know if any step you are finding confusing or difficult to understand.

Thanks and Regards

Sabyasachi

[v-janeyg-msft]

Hi, @Anonymous 

I read your description and found that the content of my first response was not problematic. The only point of confusion is:

The boundaries between 'before and after' and 'between and' are different. Are you sure you want to calculate the value excluding these two days?

Best Regards

Janey Guo