How to use Date Slicer dates to categorize data

[jvandoreglwd]

Hello, I am a novice PowerBI user, and am stuck on this issue. 

The org where I work provides services to persons over a period of years. Our underlying data in MS-SQL has about 80,000 rows of data, with dates served along with demographic data. 

We have a request to show persons served and how many were served in various data ranges. The problem arises that because we serve them over a period of years, they can end up in multiple date ranges and be counted more than once. 

Customer ID	Date of Service	YOB	Age at Date of Service	Age Grouping
222222	1/21/2018	1949	69	60-69
222222	2/10/2020	1949	71	70-79
333333	2/10/2019	1941	78	70-79
333333	3/15/2020	1941	79	70-79

In the example above, customer 222222 would be counted twice when counting persons in different age groupings. 

We use a date slicer for the Date of Service. I'd like to count the person in the maximum age range they have in the date range selected by the user in date slicer. 

So in the example above, if the user selected the date slicer range to be the calendar year 2018, then customer 222222 would be counted in the 60-69 age grouping. 

But if the user selected the date slicer for 1/1/2018 - 12/31/2020 then the customer would only be counted once, in the 70-79 age grouping. 

Any suggestions to solve this would be very appreciated. 

[V-lianl-msft]

Hi @jvandoreglwd ,

You can try to create a measure like below and apply it to visual level filter:

Measure =
var max_date = CALCULATE(MAX('Table (3)'[Date of Service]),FILTER(ALLSELECTED('Table (3)'),'Table (3)'[Customer ID]=MAX('Table (3)'[Customer ID])))
return IF(max_date=MAX('Table (3)'[Date of Service]),1,0)

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

[jvandoreglwd]

Unfortunately, neither of the above solutions worked. 

It seems like I should be able to:

MEASURE = 

VAR _date = MAX('Table'[Date of Service])  <- to ascertain what was the maximum date that the user selected in the slicer. 

VAR _YrSelected = Year(_date)

VAR _Age = _YrSelected - 'Table'[YOB]  <- subtract the customer's year of birth from the year selected. 

return

switch to sort ages into buckets. 

but I can't figure out how to calculate the age for **each** customer. 

@amitchandak @V-lianl-msft 

[amitchandak]

@jvandoreglwd , all your measures should follow this format

This is measure for Age Grouping

Measure =
VAR __id = MAX ('Table'[Customer ID] )
VAR __date = CALCULATE ( MAX('Table'[Date of Service] ), ALLSELECTED ('Table' ), 'Table'[Customer ID] = __id )
CALCULATE ( MAX ('Table'[Age Grouping] ), VALUES ('Table'[Customer ID] ),'Table'[Customer ID] = __id,'Table'[Date of Service] = __date )