Count aging dynamically and place in bucket

[ashraf_K]

Hello,

I am trying to calculate the aging for customers based on a date slicer, put the ages in buckets and count the number of customers.

I already calculated the age from the DOB dynamically.

age = 
var _max = maxx(allselected(Calender),Calender[Date])
return
datediff(min(DIM_MEMBER_FAMILY[DTBIRTH]),_max,YEAR)

I was also able to classify them in groups.

Bucket = 

var bucket = SWITCH(TRUE(),[age]<19,"1-18",[age]<30,"19-29",[age]<46,"31-45",[age]<61,"45-60","60+")

return bucket

The result is something like this 

However i am not able to do the count on the buckets.

It should be something like this.

Bucket	Count
1-18	87
19-29	114
31-45	98
45-69	54
60+	111

EDIT: link to data and pbix.

https://1drv.ms/u/s!AnVDX6Lo3zJakyevd6TvJ_kGqUZ7?e=T43zQ6

Any help would be much appreciated.

Thanks

[Ashish_Mathur]

Hi,

Share the link from where i can download the PBI file.

[ashraf_K]

https://1drv.ms/u/s!AnVDX6Lo3zJakyevd6TvJ_kGqUZ7?e=T43zQ6

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[JB_AT]

@Ashish_Mathur The file doesn't exit anymore, can you provide the steps you took to get the result?

Thanks

[ashraf_K]

Hello JB_AT,

You can access the file from this link.

[JB_AT]

Your file is exactly what I am trying to achieve, but is it possible to achieve the same result using measures? I have opened a post here explaining my issue

 https://community.fabric.microsoft.com/t5/Desktop/Dynamic-Buckets/m-p/3521814 

I have a history table with multiple rows for an ID, that's why I am using Running Totals. I basically want to capture the Years of Service for the Date Range I choose in my slicer

Thank you

[JB_AT]

Thank you!

[Ashish_Mathur]

Hi,

It is an old post and i do not remember the steps/question.  Share data in a format that can be pasted in an MS Excel file, explain the question and show the expected result.

[ashraf_K]

Hello Ashish,

This does point me in the right direction.

The solution i was trying to implement was to be able to calculate the age of the IDs dynamically from the slicer. For example if the DOB is 01/01/2000 and i select 2018 in the slicer, it should add 1 to the bucket 1-18.
However, if i select 2019 it should add 1 to the bucket 19-29.

Regards,

Ashraf Khadaroo

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution in this PBI file.

Hope this helps.

[ashraf_K]

Hello Ashish,

This is exacly the solution. Thank you very much.

Regards,

Ashraf

[Ashish_Mathur]

You are welcome.

[lbendlin]

You need to do that count in an independent measure.

Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to assist you. I cannot use screenshots of your source data.

Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

[ashraf_K]

https://1drv.ms/u/s!AnVDX6Lo3zJakyevd6TvJ_kGqUZ7?e=T43zQ6