Number of distinct users per service and survey result filtered with date slicer

[Anonymous]

Hi,

I have data on user surveys like this:

The classification given to each user as a result is always valid until the user fills in another survey. So in my example data for example for user 1 classification was 1 between January 1st and July 31st and from August 1st onwards the classification is 4.

Now what I would have to do is to calculate how many distinct users there were per each service per each classification AND I would need to use a date slicer to define on which time range I would like to see the number of distinct users. And each user should be recorded there only was and their most recent classification value should be used. So e.g. when looking at year 2022 for service X user 1 should be calculated only under classification 4, not under classification 1.

I have created a separate calendar/Date table.

I have tried a lot of things, as my last option a summarized/(grouped) table where I grouped it by CustomerID and created a column with MAX(SurveyDate) but that obviously will not dynamically change based on date slicer to show the newest survey result on that date range...

Does anyone have suggestions how to solve this? I am quite new to solving questions like this in PowerBI so all ideas are appreciated... Thanks!

[amitchandak]

@Anonymous , Create a second date first, that ill like end date

end Date=

var _date= minx(filter(Table, [Customer ID] = earlier([Customer ID]) && [Start Date] > earlier([Start Date]) ), [Start Date])

return

if(isblank(_date), blank(), _date -1)

[Anonymous]

Thanks @amitchandak , managed to do as you instructed! Will continue to try to solve rest of the problem with this.