Finding Historical Data

[Anonymous]

Hi All, 

I have a dataset similar to the below.

ID	On Hire Date	Off Hire Date
1
2	01/01/2021	30/01/2021
3	01/02/2021
4	01/03/2021	10/03/2021
5	01/04/2021	25/06/2021

I have created a visual which tells me how many ID's are On Hire based on todays data

Logic - Count of ID's filtered by On Hire Date is set, Off Hire Date is blank

I want to create another visual that tells me how many ID's are On Hire based upon a certain data. For example, if a date slicer was set back to 05/03/2021 it would state how many ID's where On Hire at that certain time. Looking at the above dataset this would return a value of 2 for ID's 3 & 4.

I would also require a feature to see the average amount on hire per month? This visual would require a data difference timeline slicer and take the average amount of ID's on hire over the selected perioid.

How can this be achieved?

Many thanks 🙂 

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try:

On Hire based on today = CALCULATE(COUNTROWS('Table'),FILTER('Table',[On Hire Date]<>BLANK()&&  [On Hire Date]<=TODAY() && [Off Hire Date]=BLANK()))

If you want to calculate the count based on some selections, please firstly create a new calendar table with Dates for slicer

Calendar = CALENDAR(MIN('Table'[On Hire Date]), MAX('Table'[Off Hire Date]))

On Hire at certain time = CALCULATE(COUNTROWS('Table'),FILTER('Table',[On Hire Date]<>BLANK()&&  [On Hire Date]<=MAX('Calendar'[Date]) && ([Off Hire Date]=BLANK()|| [Off Hire Date]>=MAX('Calendar'[Date]))))

On Hire over selected perioid = CALCULATE(COUNTROWS('Table'),FILTER('Table',[On Hire Date]<>BLANK() && [On Hire Date]<=MIN('Calendar'[Date]) && ( [Off Hire Date]>=MAX('Calendar'[Date]) || [Off Hire Date]=BLANK())))

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

[amitchandak]

@Anonymous , Where is the amount column?

refer if this blog can help

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.