Slicer Hire Duration

[Serdet]

Hi All,

I have a dataset similar to the below. 

I want to create a slicer which when individual buttons are selected will show ID's that have been on hire for 1 month, 2 months, 3months etc. 

ID	On Hire Date	Off Hire Date
1	01/01/2019	01/02/2019
2	01/01/2019	01/03/2019
3	01/01/2019	01/04/2019
4	01/01/2019	01/05/2019

Can anyone please help?

[v-jayw-msft]

Hi @Serdet ,

Create a table as below and use the month column as silcer.

Then create a measure as below and add it to visual filter.

Measure = IF(DATEDIFF(SELECTEDVALUE('Table'[On Hire Date]),SELECTEDVALUE('Table'[Off Hire Date]),MONTH)<=SELECTEDVALUE('month'[value]),1,0)

Result would be shown as below.

Best Regards,

Jay

[Serdet]

Hi Jay,

Thanks for your comment. I have followed the advised steps and have the below outcome.

• When a month is selected the visual I am hoping to effect will lose all of its changed settings (i.e font etc) and it will not show any results.

Please can you advise?

[v-jayw-msft]

Hi @Serdet ,

I don't get the point, could you explain the details?

Best Regards,

Jay

[Fowmy]

@Serdet 

Can you provide more information and explain further? 
What do you need to select/filter? is it Period as months?, How does Off Dates come in to play?
Show an expected output as well to better understand your requirement.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

I accept KUDOS 🙂

YouTube, LinkedIn

[Serdet]

Hi,

I am looking at scaffold data and have an On Hire and Off Hire date. From this, I want to create a slicer which will show certain clickable boxes (i.e 1 month, 2 months, 3 months) and when these boxes are selected it will show me all scaffolds that have been on hire longer than the selected time frame.

Sorry for the jaba jaba.

[amitchandak]

@Serdet , refer if this can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

or a slicer like this

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

and formula like

Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-3,MONTH))
Rolling 3 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-3,MONTH))