30/60/90 pie chart based on criteria

[brizzdizz]

Hi, I'm trying to create a 30/60/90 pie chart that shows the number of unpaid invoices where:
1) <30 = 1-29 days, 30 = 30-59 days, 60 = 60-89 days, >90 = 90+ days

2) It only includes the invoice in the calculation when "Payment Amount" is blank

Sample data:

[brizzdizz]

Thank you so much. This is very close but I noticed my measure for >90 wasn't picking anything up. For my data I expect to have 116 in the >90 category but nothing is shown. In checking the "pie chart based on criteria.pbix" file that was attached, I see it has the same issue where the 187 daydiff should have been picked up as >90 but it wasn't. I've made changes to the measure to attempt to get it to work but haven't been successful. Any ideas?

[v-xiaotang · Accepted solution]

Hi @brizzdizz 

Thanks for your reply.

When day>90, _end returned null, so I added if(). Try this measure,

Measure = 
var _start= MIN(days[day])
var _end= MINX(FILTER(all(days),days[day]>_start),days[day])
return IF(_end<>BLANK(),CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[daydiff]>=_start && 'Table'[daydiff]<_end)),CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[daydiff]>=_start )))

Best Regards,

Community Support Team _Tang

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

[brizzdizz]

This worked perfectly. Thank you!

[v-xiaotang]

Hi @brizzdizz 

Thanks for reaching out to us.

You can try this,

(1) create the table first

(2) create the measures below

daydiff = IF(ISBLANK(MIN('Table'[Payment Amount])),DATEDIFF(MIN('Table'[Payment Due Date]),TODAY(),DAY))

Measure = 
var _start= MIN(days[day])
var _end= MINX(FILTER(all(days),days[day]>_start),days[day])
return CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[daydiff]>=_start && 'Table'[daydiff]<_end))

result

Best Regards,

Community Support Team _Tang

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