Forum Discussion
Date Slicers for Visuals
Hello,
Pretty new to Power BI so please excuse if this is a repeat question. I've tried searching for a solution for a couple of days now and am getting nowhere with the solutions I have found. Basically I would like to create a visual that lists the number of clients of we have grouped by their age. My table is coming from a view that I've created in SQL that has the following:
| Client ID | Start Date (DD/MM/YYYY) | End Date (DD/MM/YYYY) | Date of Birth (DD/MM/YYYY) | Place Type |
| 1 | 25/06/2020 | 15/01/2021 | 15/12/2016 | A |
| 1 | 15/01/2021 | NULL | 15/12/2016 | C |
| 2 | 01/01/2019 | 02/02/2020 | 31/03/2018 | B |
| 2 | 02/02/2020 | 03/01/2020 | 31/03/2018 | A |
| 2 | 03/01/2020 | 25/06/2021 | 31/03/2018 | A |
| 3 | 01/12/2017 | 25/01/2020 | 26/06/2005 | A |
| 3 | 25/01/2020 | NULL | 26/06/2005 | A |
| 4 | 06/09/2020 | 25/11/2020 | 05/12/2015 | C |
| 5 | 15/05/2020 | NULL | 24/08/2014 | B |
What I would like to do is the following:
- Visual 1 - Show only the clients that are active at the selected date by Place Type
- Visual 2 - Show the age of the clients that are active at the selected date
The filters I have set up currently are: End Date (filter type is "After") and Start Date (filter type is "Before"). The way the filtering should work, which I can do easily in SQL is basically like: return all clients where (Start Date <= Selected Date AND (End Date IS NULL or End Date > Selected Date)).
I'm probably describing something that is super easy to do - I feel like I am over-complicating things! I currently have a report working, based on the 2 date filters (selecting the same date for both filters) that shows the breakdown (although I feel like I should be able to use only one date...) but for the life of me can't create a measure for the age to calculate properly based on the selected date!
I've also tried creating a "Date" table and, using that, I can get the Age calculating properly BUT it doesn't show the Active clients properly for the selected date.
Any and all help will be much appreciated - although I would prefer mockery be kept to a minimum 🙂
Try this solution (MM/DD/YYYY format).
1. Create a date table SlicerDate that has no relationships.
2. Create a date slicer using SlicerDate[Date].
3. Create measure:
Active Clients = VAR vSlicerDate = SELECTEDVALUE ( SlicerDate[Date] ) VAR vStartDate = MAX ( FactTable[Start Date (DD/MM/YYYY)] ) VAR vEndDate = MAX ( FactTable[End Date (DD/MM/YYYY)] ) VAR vEndDateAdj = IF ( ISBLANK ( vEndDate ), DATE ( 9999, 12, 31 ), vEndDate ) VAR vResult = IF ( vSlicerDate >= vStartDate && vSlicerDate <= vEndDateAdj, 1 ) RETURN vResult4. Create a filter in the visual using the measure [Active Clients]:
5. Result:
The concept is to use a disconnected date table, and control the filtering via DAX.
As far as mockery, DAX makes a mockery of us all. 🙂
6 Replies
- DataInsights
Super User
Try this solution (MM/DD/YYYY format).
1. Create a date table SlicerDate that has no relationships.
2. Create a date slicer using SlicerDate[Date].
3. Create measure:
Active Clients = VAR vSlicerDate = SELECTEDVALUE ( SlicerDate[Date] ) VAR vStartDate = MAX ( FactTable[Start Date (DD/MM/YYYY)] ) VAR vEndDate = MAX ( FactTable[End Date (DD/MM/YYYY)] ) VAR vEndDateAdj = IF ( ISBLANK ( vEndDate ), DATE ( 9999, 12, 31 ), vEndDate ) VAR vResult = IF ( vSlicerDate >= vStartDate && vSlicerDate <= vEndDateAdj, 1 ) RETURN vResult4. Create a filter in the visual using the measure [Active Clients]:
5. Result:
The concept is to use a disconnected date table, and control the filtering via DAX.
As far as mockery, DAX makes a mockery of us all. 🙂
Thanks DataInsights! I am getting some weird results but I have a feeling it's because of other things I have done through this whole process. When I pull the information into a Table visual there are no issues but as soon as I pull in into a stacked column visual it doesn't pull in all of the active Clients. I marked your solution as an accepted solution as it is working. Thank you for your reply and insight into this. I'm going to start with a clean slate to make sure I am on the right track again.
- DataInsights
If starting with a clean slate doesn't resolve the issue, attach a screenshot of the stacked column visual (and the expected result) and I'll take a look. It might be due to not having Start Date/End Date in the visual, so the DAX would need to be adjusted.
