Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
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 🙂
Solved! Go to Solution.
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
vResult
4. 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. 🙂
Proud to be a 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
vResult
4. 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. 🙂
Proud to be a Super User!
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.
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.
Proud to be a Super User!
So I am hoping the attachments will show you the results. Visual 1 (I've confirmed it to be correct - just uses 2 dates that are actually the same date to show the results) shows what I get with my current solution and Visual 2 shows what I get when trying to use just the one date with the "Active Client" filtered to 1. I also have the age calculating properly but getting that into a chart is another challenge that I will takle later 🙂
Thanks again!!
Try these measures. The concept is to filter FactTable for the rows that correspond to the date slicer, and then calculate the count of Client ID in the context of the filtered FactTable.
Client Count = COUNT ( FactTable[Client ID] )
Active Clients =
VAR vSlicerDate =
SELECTEDVALUE ( SlicerDate[Date] )
VAR vTable =
FILTER (
FactTable,
VAR vStartDate = FactTable[Start Date (DD/MM/YYYY)]
VAR vEndDate = FactTable[End Date (DD/MM/YYYY)]
VAR vEndDateAdj =
IF ( ISBLANK ( vEndDate ), DATE ( 9999, 12, 31 ), vEndDate )
RETURN
vSlicerDate >= vStartDate
&& vSlicerDate <= vEndDateAdj
)
VAR vResult =
CALCULATE ( [Client Count], vTable )
RETURN
vResult
Proud to be a Super User!
@DataInsights Thanks so much for your time on this and my apologies for the delayed response (just getting back up to speed from my return from vacation).
The change definitely works for a new dashboard that I was working on! Still an issue with the original, which makes me think I did something really funky. I know it works though based on my other dashboard. Thanks again!!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |