Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

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

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

Dataflow
Need Help

7 Replies

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        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