Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
jvandoreglwd
Regular Visitor

How to use Date Slicer dates to categorize data

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 IDDate of ServiceYOBAge at Date of ServiceAge Grouping
2222221/21/201819496960-69
2222222/10/202019497170-79
3333332/10/201919417870-79
3333333/15/202019417970-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. 

3 REPLIES 3
V-lianl-msft
Community Support
Community Support

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)

Vlianlmsft_0-1646120128752.png

 


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

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
Super User
Super User

@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 )

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.