Forum Discussion
Date Slicers for Visuals
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. 🙂
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. 🙂
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.
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!!
- DataInsights
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