Forum Discussion
Finding Historical Data
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 🙂
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.
2 Replies
Anonymous , Where is the amount column?
refer if this blog can help
If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.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.
