Creating Buckets based on Date DIFF formula that calculates by filtering two different tables

[aggiebrown]

Hi all,

Need help with a simple measure (I think) 

New to DAX (swtiching from EXCEL Formulas gives me a bit of a headache).

I need to calculate how many Accounts, that had a sale and have been placed on Retention plan, have then been cancelled. From there I want to see the period in which they have been cancelled (eliminating any negative calc, as obv issue with data, but I can just filter that out), and from there bucket those periods to 0-30 days, 31-60 days, 61-90 days and 90+

I have been able to achieve the Cancellation count, and Date Diff, but I am struggling to get to the bucket end of it. Tried to create a measure for Cancellation Buckets, as Text String, and also count based on the [#Cancelled within x days] measure with no success.

Sample data is provided, your help would be greatly appreciatd.

Sample Data to Download 

[v-yalanwu-msft]

Hi @aggiebrown  ,  

You could create measures by the following formula:

Cancelled #30 Days2 = COUNTX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]<=30),[# Cancelled within x days])

Cancelled #61-90 Days2 = COUNTX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]<=90&&[# Cancelled within x days]>60),[# Cancelled within x days])

Cancelled #90+ Days = COUNTX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]>90),[# Cancelled within x days])

The final output is shown below:  

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggiebrown]

@v-yalanwu-msft Many thanks for that, we are almost there but it needs to calc the actual #cancellations so there is 8 Cancellations within 30 days in that example if that makes sense?

[v-yalanwu-msft · Accepted solution]

Hi @aggiebrown  ,  

Do you mean to sum up [# Cancelletions on Retention Plan],if so,you could create measures by the following formula:

Cancelled #30 Days2 = SUMX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]<=30),[# Cancelletions on Retention Plan])

Cancelled #61-90 Days2 = SUMX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]<=90&&[# Cancelled within x days]>60),[# Cancelletions on Retention Plan])

Cancelled #90+ Days = SUMX(FILTER(ALL('Acc_ID'),[# Cancelled within x days]>90),[# Cancelletions on Retention Plan])

The final output is shown below:  

if not ,Can you share the result and logic you want？

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[aggiebrown]

That has worked - thank you!

[amitchandak]

@aggiebrown , You need to have datediff column on some common dimension say order number

average(values(Order[Order ID], datediff(min(Sales[salesdate]), max(account[cancel date]), day))

refer  my blog for that: https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...

Once you have this measure; create an independent bucket with start and end limit. You have to create new measures that can use this bucket.

I have explained the same here

Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-k

[aggiebrown]

@amitchandak Hey, I saw your video, but you're creating an extra table, which I don't want to do. I want to be able to achieve this with a measures instead.

If you open my sample, you will see I already have a date diff calculation that works. I just need help with bucketing based on that. The data model is not a snowflake - there is 2 data tables that need to be filtered out.