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 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
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!!