cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rwaugh
Frequent Visitor

Date Slicers for Visuals

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 IDStart Date (DD/MM/YYYY)End Date (DD/MM/YYYY)Date of Birth (DD/MM/YYYY)Place Type
125/06/202015/01/202115/12/2016A
115/01/2021NULL15/12/2016C
201/01/201902/02/202031/03/2018B
202/02/202003/01/202031/03/2018A
203/01/202025/06/202131/03/2018A
301/12/201725/01/202026/06/2005A
325/01/2020NULL26/06/2005A
406/09/202025/11/202005/12/2015C
515/05/2020NULL24/08/2014B

 

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 🙂 

1 ACCEPTED SOLUTION
DataInsights
Super User
Super User

@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]:

 

DataInsights_1-1625071119605.png

 

5. Result:

 

DataInsights_0-1625071072276.png

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

View solution in original post

6 REPLIES 6
DataInsights
Super User
Super User

@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]:

 

DataInsights_1-1625071119605.png

 

5. Result:

 

DataInsights_0-1625071072276.png

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.

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

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

 

Visual 1.pngVisual 2.png

@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

 

DataInsights_0-1626008985601.png

 

DataInsights_2-1626009066793.png

 

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

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors