Conditional Measure using slicer selection

[Rob92]

I am trying to put together a stacked column chart, using the example table below, I want to show total transactions per month. The one color of the stacked column will show transactions by clients who joined before the start of the selected date range (marked those in bold below), and the other color of the bar will be transactions by clients who joined after the start of the selected date range (marked those in italics below).  The user needs the ability to chose the date range with a slice.  In this example let's say the user has chosen 1/1/24 - 3/31/24.  So the Jan column would show a total of 18 transactions (16 from old clients, 2 from new), Feb would show 24 transactions (14 from old clients, 10 from new), and Mar would show 33 transactions (13 from old clients, 20 from new).

If the user changes the date range, the clients that are considered old or new will change.  Here if they changed the range to 2/1/24 - 3/31/24, then client C would change from the new catagory to old.

Since the user needs control of the date range, I can't code these catagories into the datasource. I know I can't reference a single slicer value in a calculated column formula.  So I'm trying to figure out if I can achieve this with a measure, using SWITCH and SELECTEDVALUE to check if a Client_joined date is before or after the start of the date range the user sets, and catagorize the transactions in the visual dynamically that way. Any thoughts on this?  Or any other ways I could achieve this result?  Thanks in advance!

Client	Client_joined	Transactions	Transaction_month
A	5/5/2023	9	Jan-24
B	10/10/2023	7	Jan-24
C	1/20/2024	2	Jan-24
A	5/5/2023	8	Feb-24
B	10/10/2023	6	Feb-24
C	1/20/2024	7	Feb-24
D	2/15/2024	3	Feb-24
A	5/5/2023	8	Mar-24
B	10/10/2023	5	Mar-24
C	1/20/2024	6	Mar-24
D	2/15/2024	8	Mar-24
E	3/5/2024	6	Mar-24

[v-yiruan-msft · Accepted solution]

Hi @Rob92 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a sperated date dimension table(DO NOT create any relationship with your fact table) just as suggested by Ibendlin

2. Create two measures as below

Old clients count = 
VAR _mindate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Transactions] ),
        FILTER ( 'Table', 'Table'[Client_joined] <= _mindate )
    )

New clients count = 
VAR _mindate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Transactions] ),
        FILTER ( 'Table', 'Table'[Client_joined] > _mindate )
    )

3. Create a table visual as shown in the below screenshot

Best Regards

[Rob92 · Accepted solution]

Awesome, thanks guys!  I have implemented the solution on my real data and it works a treat.  The only issue was that as well as the slicer controlling which users are new or old, I also need it to filter the visual.  In order to do this I have joined the date dimension table to the fact table.  This causes the date hierachy to break but that's not a problem - to resolve that, I added separate month and year columns in power query, and used those on the X axis instead.  Problem solved!

[v-yiruan-msft · Accepted solution]

Hi @Rob92 ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a sperated date dimension table(DO NOT create any relationship with your fact table) just as suggested by Ibendlin

2. Create two measures as below

Old clients count = 
VAR _mindate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Transactions] ),
        FILTER ( 'Table', 'Table'[Client_joined] <= _mindate )
    )

New clients count = 
VAR _mindate =
    MIN ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Transactions] ),
        FILTER ( 'Table', 'Table'[Client_joined] > _mindate )
    )

3. Create a table visual as shown in the below screenshot

Best Regards

[lbendlin]

You need to use a disconnected table to feed your date range slicer, and then use measures to filter your visual.