DAX for filter with modified date and version

[report_freak]

hi

not sure if this is possible for DAX in powerbi i am trying to do a filter to count the row when the year change and get any latest "version" from that year. example

this my table

Name	Date of Birth	Version	Remarks	Modified Date
Phoebe Parker	28/1/2002	1	Hired	28/1/2008
Krystal Pratt	14/5/1981	1	Hired	28/2/2008
Krystal Pratt	14/5/1981	2	Resigned	26/7/2009
Ayla Bartlett	23/2/1982	1	Hired	22/1/2010
Ayla Bartlett	23/2/1982	2	Change of title	24/4/2011
Ayla Bartlett	23/2/1982	3	Resigned	10/5/2011
Yurem Bridges	11/12/1983	1	Hired	9/1/2013
Yurem Bridges	11/12/1983	2	Change of address	24/7/2014
Mariyah Cross	7/3/1986	1	Hired	8/2/2015
Yurem Bridges	11/12/1983	3	Change of department	27/7/2015
Alonso Leach	15/5/2004	1	Hired	3/9/2015
Yurem Bridges	11/12/1983	4	Change of title	3/9/2015
Mariyah Cross	7/3/1986	2	Resigned	28/9/2015
Elliot Franklin	13/5/1986	1	Hired	4/4/2016
Dakota Sheppard	24/6/1986	1	Hired	23/10/2016
Gwendolyn Bush	9/12/2002	1	Hired	23/10/2016
Rachael Blankenship	30/9/1987	1	Hired	10/1/2017
Owen Hickman	1/8/1991	1	Hired	2/3/2018
Rachael Blankenship	30/9/1987	2	Change of title	2/4/2018
Jasmin Kim	12/10/1988	1	Hired	8/11/2018
Rachael Blankenship	30/9/1987	3	Change of department	21/7/2019
Malachi Delgado	31/12/1992	1	Hired	30/3/2021
Desiree Murphy	12/1/1994	1	Hired	7/7/2021
Elijah Webb	25/3/2002	1	Hired	7/7/2021
Haylie Saunders	13/5/1994	1	Hired	12/8/2021
Amelia Meza	20/7/1995	1	Hired	31/8/2021
Callie Campbell	19/6/2005	1	Hired	31/8/2021
Jerome Sloan	9/10/1995	1	Hired	2/1/2022
James Stone	17/7/2000	1	Hired	4/1/2022

and if i filter for 2021 below is the expected result that i need.

Name	Date of Birth	Version	Remarks	Modified Date
Phoebe Parker	28/1/2002	1	Hired	28/1/2008
Alonso Leach	15/5/2004	1	Hired	3/9/2015
Yurem Bridges	11/12/1983	4	Change of title	3/9/2015
Elliot Franklin	13/5/1986	1	Hired	4/4/2016
Dakota Sheppard	24/6/1986	1	Hired	23/10/2016
Gwendolyn Bush	9/12/2002	1	Hired	23/10/2016
Owen Hickman	1/8/1991	1	Hired	2/3/2018
Jasmin Kim	12/10/1988	1	Hired	8/11/2018
Rachael Blankenship	30/9/1987	3	Change of department	21/7/2019
Malachi Delgado	31/12/1992	1	Hired	30/3/2021
Desiree Murphy	12/1/1994	1	Hired	7/7/2021
Elijah Webb	25/3/2002	1	Hired	7/7/2021
Haylie Saunders	13/5/1994	1	Hired	12/8/2021
Amelia Meza	20/7/1995	1	Hired	31/8/2021
Callie Campbell	19/6/2005	1	Hired	31/8/2021

or if i filter 2017 i will expect this

Name	Date of Birth	Version	Remarks	Modified Date
Phoebe Parker	28/1/2002	1	Hired	28/1/2008
Alonso Leach	15/5/2004	1	Hired	3/9/2015
Yurem Bridges	11/12/1983	4	Change of title	3/9/2015
Elliot Franklin	13/5/1986	1	Hired	4/4/2016
Dakota Sheppard	24/6/1986	1	Hired	23/10/2016
Gwendolyn Bush	9/12/2002	1	Hired	23/10/2016
Rachael Blankenship	30/9/1987	1	Hired	10/1/2017

[report_freak · Accepted solution]

i manage to resolved it myself, below is the measure dax i used.

Headcount = 
//get max filted Date
Var MaxDate=MAX ( Dates[Date] )
//Filter down the Moditifed date to the Max Date
Var FirstFilter = FILTER (Employees, Employees[Modified Date] <= MaxDate)
//Rename Version and Name column so its not confusing
var allrecord = SELECTCOLUMNS(FirstFilter,"allVersion",Employees[Version],"allName",Employees[Name])
//add addtional column to reverse the Version number so that the latest version will always be 1
var RecordsFilter = ADDCOLUMNS(FirstFilter,"VersionR",MAXX(FILTER(allrecord,[allName]=Employees[Name]),[allVersion])+1 - Employees[Version])

return
//Count the rows where Version number = 1
   COUNTROWS(FILTER(RecordsFilter,[VersionR]=1))

[report_freak]

Anyone? 

[punitkhatri]

 You'll need the following measures :-

ResultCondition =

VAR selyear = SELECTEDVALUE('Date'[Year])

VAR FilYear =

CALCULATE(

    COUNTROWS(Filterchange),//SUM(Filterchange[Version]),  

        Filterchange[Mdyear] <= VALUE(selyear) //VALUE(SELECTEDVALUE('Date'[Year]))

    ,

    ALL('Date')

)

RETURN

IF(FilYear <> 0, 1, 0)

Versions = CALCULATE(SUM(Filterchange[Version]), ALL('Date'))

Now Plot all the columns that you need in a table but use the "Versions" measure as the replacement of the Version column. It will be helpful in not letting the date table filter our visual. And ideally also, we should plot measures instead of columns in the visuals wherever possible.

Now apply the ResultCondition measure as a visual level filter on the same visual and set its value = 1.

Helped by @KeyurPatel14  so a special thanks to this guy.

Let me know If this helps or not.

[report_freak]

Hi @punitkhatri  and @KeyurPatel14 

thanks for your guidance, is it possible to filter it further to just one record of the latest version per name?