DAX measure based on two slicer outputs

[Miaouss_1060]

Hello everyone!

I am trying to solve following puzzle, but fail until now, even though I spent quite some time looking for similar problems.

I have following table:

Customer	Month	Attribute
Armand	January	'active'
Armand	February	'active'
Armand	March	'active'
Bénédicte	January	'inactive'
Bénédicte	February	'inactive'
Bénédicte	March	'inactive'
Claude	January	'active'
Claude	February	'active'
Claude	March	'inactive'
...	...	...

I also have two slicers both based on table[Month] to select a start month and an end month.

I try to figure out a DAX formula for a measure to get the distinct count of customers that went from attribute "active" to "inactive", based on the selected start month and selected end month. (Eventually to display this measure in a KPI card.)

E.g.: selected start month is January, selected end month is February → my measure should return 0.

E.g.: selected start month is January, selected end month is March → my measure should return 1 (only Claude was active in January and inactive in March).

I tried several approaches, including some involving a duplication of the table, but without success until now. I would be very greateful for your help!
Best regards.

[Anonymous · Accepted solution]

Hi @Miaouss_1060 ,

Please try this way:
First of all I would recommend that you use a numerical form to record the month, as numbers are easier to use for calculations.
Here is my sample data:

Use this DAX to create a measure:

Measure = 
VAR _start = MIN('Table'[MonthNo])
VAR _end = MAX('Table'[MonthNo])
VAR ActiveCustomersStart = 
CALCULATETABLE(
    VALUES('Table'[Customer]),
    'Table'[Attribute] = "active",
    'Table'[MonthNo] = _start
)
VAR InactiveCustomersEnd = 
CALCULATETABLE(
    VALUES('Table'[Customer]),
    'Table'[Attribute] = "inactive",
    'Table'[MonthNo] = _end,
    'Table'[Customer] IN ActiveCustomersStart
)
RETURN
COUNTROWS(InactiveCustomersEnd)

The final output is as below:

If you must need two slicers, I suggest you to add another two tables for slicers without any relationships:

And change the measure into this:

Measure 2 = 
VAR _start = SELECTEDVALUE('Start'[MonthNo])
VAR _end = SELECTEDVALUE('End'[MonthNo])
VAR ActiveCustomersStart = 
CALCULATETABLE(
    VALUES('Table'[Customer]),
    'Table'[Attribute] = "active",
    'Table'[MonthNo] = _start
)
VAR InactiveCustomersEnd = 
CALCULATETABLE(
    VALUES('Table'[Customer]),
    'Table'[Attribute] = "inactive",
    'Table'[MonthNo] = _end,
    'Table'[Customer] IN ActiveCustomersStart
)
RETURN
COUNTROWS(InactiveCustomersEnd)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miaouss_1060]

Hello v-junyant-msft,
Your solution works perfectly! Thanks a lot! 😄
Best regards.

[Miaouss_1060]

Hello johnbasha33,

Thanks for the very fast reply!

Unfortunately the measure returns "empty"; except if I replace the "active" by "inactive" in the formula: in that case I get the total number of customers in my table (independently from the selected start and end months).
A precision: it is possible that a customer switches back from "inactive" to "active" (not an illustrated case in my table).
I am trying to make your formula work (no success for now); I wonder if the LASTNONBLANK() function could be the issue (I at least am not sure on how it is intended to work).
Best regards.

[johnbasha33]

@Miaouss_1060 

Try the below one
TransitionedCustomers =
VAR SelectedStartMonth = SELECTEDVALUE('SlicerStartMonth'[Month])
VAR SelectedEndMonth = SELECTEDVALUE('SlicerEndMonth'[Month])
RETURN
COUNTROWS(
FILTER(
VALUES('Table'[Customer]),
CALCULATE(
LASTNONBLANK('Table'[Attribute], 1),
FILTER(
ALL('Table'),
'Table'[Month] = SelectedStartMonth &&
'Table'[Customer] = 'Table'[Customer]
)
) = "active" &&
CALCULATE(
LASTNONBLANK('Table'[Attribute], 1),
FILTER(
ALL('Table'),
'Table'[Month] = SelectedEndMonth &&
'Table'[Customer] = 'Table'[Customer]
)
) = "inactive"
)
)