Entry with Max Date

[soupyNoodles]

Apologies if this is posted somewhere else already.

My fact table is as such: there is an entry for each person's interaction with a company. There is a date associated with each interaction and the person's race/ethnicity, age, and gender are recorded at each service date. I am throwing into a dashboard various visualizations for these people but I want to filter them by the latest service they had; a person's race/ethnicity can be incorrectly recorded (so I want the latest one which is recorded at the latest service), and a person's gender can change over time, as does the age. I am building the source table via SQL and would normally use a lead () window function but I want to achieve this in Power BI. 

If, say, I were to apply an IsLatestInteraction = 1 filter from SQL Server to the Power BI visualization, and restricted the service dates from say January 1, 2023, to March 1, 2023 in Power BI via a service date slicer, the visualization would miss people who were serviced in June 2023 because that is when their latest service was. I want to build a measure (if that is what I should be using??) where it calculates the person's latest age, race/ethnicity, and gender AS OF the date slicer selected. I am confused between the various Power BI functions such as ALL and ALLEXCEPT but I believe some sort of DAX function needs to be used. Please let me know what is best! Thanks in advance!

[some_bih]

Hi @soupyNoodles it seems you need standard star schema model in Power BI. Service seems like sales in Contoso model for Power BI.

For last sales / service date according to this model on dax.do you can use something like below (simple max date of sales) with few columns

https://dax.do/NsCjd1Sto0Lh0W/ 

Check link for example of diagram on dax.do

On the link below https://dax.do/diagram/ 

Hope this help, kudos appreciated.

[soupyNoodles]

Apologies for the slow reply. While this does help if my output is a row-level table, what I intend to do is create a visualization. For instance, if I wanted to show a breakdown at the race/ethnicity level, the challenge is that the measure above selects the latest date as of the whole dataset. I have tried many variations of this using ALLEXCEPT, for instance, and I need the output to select the latest date as of a date slicer PER PERSON and then identify the relevant race/ethnicity for my visualization.  

[some_bih]

Hi @soupyNoodles please share soome sample data of your inputs and expected output.

Yes, in example above max, last date is for overall data. So we need your details.