Forum Discussion

report_freak's avatar
report_freak
Frequent Visitor
3 years ago
Solved

DAX for filter with modified date and version

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...
Need Help
Tips and Tricks
  • report_freak's avatar
    report_freak
    3 years ago

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