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