Date Slicers for Visuals

[rwaugh]

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:

1. Visual 1 - Show only the clients that are active at the selected date by Place Type
2. 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 🙂 

[DataInsights · Accepted solution]

@rwaugh,

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. 🙂

[rwaugh]

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]

@rwaugh,

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.

[rwaugh]

@DataInsights 

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]

@rwaugh,

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

[rwaugh]

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