Rollover Slicer Data as per value selected in Date Slicer

[shelatMalavR]

Hello Users, 

I have scenario where in i want to display Employee name under Slicer dropdown such that when a user selected hiring year 2018 it should display all hiring done in year 2018, and when user selects 2019 it should display all employees hired in 2018 and 2019 and similary for 2020 it should display name of all employees hired in 2018, 2019 and 2020

Hiring year will be single select slicer, Employee name is a multi select dropdown slicer.

Any idea how can this be done ?

Thanks and regards,

Malav shelat

[shelatMalavR]

Hello Amit, 

Thanks for your reply, 

I do have independent table to Year/Date which is marked as date, however when i create a measure as you suggested it does not work as expected, so i think may be i am missing something out. so i am attaching the pbix file link:  https://www.dropbox.com/s/peglrk0hqggimtl/TestReportSampleData.pbix?dl=0   and below is the scenario:

from the pbix file if you select year 2018 in timeline slicer  for example : you will be seeing 122 employees, 
The Employeename slicer should then display on those 122,

but now when you select year 2019 , the employeename slicer should show me the all employees of 2018 i.e 122 and also 2019 newly added 18 employees, making total count of 140 employee and not just 2019 employees which will be just 18.

an example of this would an employee name karen pugh was hired in 2019, 

so in 2018 Karen pugh should not be displayed , but from 2019 onwards her name should be displayed till she is terminated. 

you can also review my zTestEmployeeCount to review how am i calculating rolling period employee counts for each year.

awaiting your opinion/solution on this one,

Thanks and regards,

Malav

[v-chenwuz-msft · Accepted solution]

Hi @shelatMalavR ,

Maybe you can try this measure and put it in filter pane on employee name slicer. But inactive the relationship between these two tables. This means an independent date/year table as @amitchandak said.

zMeasure =
VAR _allnametable =
    CALCULATETABLE (
        VALUES ( 'DEI Report Qtrly 2022'[Employee Number] ),
        FILTER (
            'DEI Report Qtrly 2022',
            [Hire Date] <= MAX ( 'Calender'[Date] )
                && [Hire Date] >= DATE ( 2018, 1, 1 )
        )
    )
RETURN
    IF (
        SELECTEDVALUE ( 'DEI Report Qtrly 2022'[Employee Number] ) IN _allnametable,
        1
    )

And set it show itmes which is 1

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@shelatMalavR , It is better to use an independent date/year table for that

//Year is independent Date table
new measure =
var _max = maxx(allselected(Year ),Year [Year ])
return
calculate( sum(Table[Value]), filter('Table', year(Table'[hire Date]) <=_max))

Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI