Using a date slicer then applying an additional date based filter on a chart

[WBscooby]

Hi

I'm still a relative newbie to Power BI so please excuse if this is simple - I just can't get my head around what I need to do.

I have a database based on offending. I have an offence table which has a person ID, offence type and offence date. I have also created columns for Year and Quarter (fiscal) based on the offence date.

A person can only have one offence per quarter but can appear in every quarter.

I need my chart to count the number of offences by offence type based on the slicer selection. If I select Quarter, this is fairly straightforward as the number of offences is the same as the number of people. However, if I select year, I only want the chart to count the earliest offence date for each person. I've tried creating a firstdate measure for the offence date but didn't know what to do with it once I had! Any advice would be great. Thank you!

A very simplified example of data below:

[Anonymous · Accepted solution]

@WBscooby 

Change it to the following measure, measure only considers the current row context, you must set it to ALl(table).

Measure = CALCULATE(FIRSTDATE('Table'[Column1]),ALL('Table'))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[HotChilli]

What does the measure you've written look like?

Also, it's good practice to create a Calendar table for data like this.  You can create a relationship between DateOffence and date in the calendar table.  Then create the Quarter, Year fields etc in the Calendar table (and not in the Fact table as shown)

[WBscooby]

It's just a basic 

FirstOffenceDate = FIRSTDATE(Offences[Date Offence])

I was hoping that I could use the slicer to filter the correct date range and then I could use this to filter the data further to the first offence date... but I get very confused with filters!

Thanks for the calendar tip. I'll add that in to the model.

Thank you